Excel Function and VBA Coding

Question

Dear Sir,

I want to know if there is a function in Excel or VBA feature in Excel wherein we can insert particular value say cell no AA87 value in Table of Cells like below table in appropriate cell based on row and column number ,

Column Number is fixed say 35 and row number is dynamic like i ( Date Value)

How can i transfer AA87 value to appropriate cell in below table , if i know row and column number clearly and is there any excel function whcih can insert these values in below table ?

Answer 1

Worksheets("Series").Cells (i, 47) = Worksheets("INPUTOUTPUT"). Range("AA87")

For this code also, it has to change between two worksheets actually.

So it is time consuming

A 100% no, it does not change the sheets and this is not time consuming, even if the code is repeated for a few hundred rows.

It might be possible that this code takes a long time to execute in your file, but the issue is not the code itself. The most reason for this are bad formulas and/or a lot of eye catchers, e.g. conditional formatting, spark lines, etc.

Go one step backwards and test the code in a new file that contains only values.

If you measure the timings between this and your file you'll find that there's a big difference.

If you need further help share your file and I'll take a look.

Andreas.

Answer 2

Dear Sir,

Please find below Table here.

Column 47 is fixed and i value will change and we will need to write each diff value AA87 in i row and column number 47

How to do this ?

Answer 3

Worksheets("Series").Cells (i, 47) = Worksheets("INPUTOUTPUT"). Range("AA87")

For this code also, it has to change between two worksheets actually.

So it is time consuming and I have around 40 such lines so, it is taking hude amount of time for exectution and i takes around 2 years dates so i 0 to 730, and then 40 such lines, so code takes long time to run

To avoid this, i need to do this data transfer in Excel itself. But i am not able to find the method yet.

Answer 4

No, i will meet my requirement with code you mentioned actually.

See table below

I will need to insert AA87 in cell appropriate in above table . Consider column starting with 45. So i willl need to insert Each value of AA87 in this column after each FOR LOOP Run case.

So, for i=1, first cell in that column will take the value of AA87 for first iteration

For i=2, It will be in second cell (which has current value of 1.673227453.

Did you get my requirement?

Answer 5

Even if i change the code to copy paste, still it will consume lot of time in VBA right ?

Worksheets("INPUTOUTPUT"). Range("AA87").copy

Worksheets("Series"). Cells (i, 47).pastespecial

The reason is that copy & paste executes a lot code inside the application that you "can not see", e.g. it accesses the clipboard and put a lot of data structures into.

Write the value only:

Worksheets("Series").Cells (i, 47) = Worksheets("INPUTOUTPUT"). Range("AA87")