excel macro: How to fill range with formula based on values of range on different sheet?

Sheet "Results" has (D12:L37) to be filled with a vlookup formula that references a corresponding cell on sheet "Calculations"

How do I write it in actual vb code, either as a loop, or simply as of lines of code. Here is what the meta-code might be:

'Results'!D12 = vlookup ('calculations'!B11,table,3)
'Results'!D13 = vlookup ('calculations'!B12,table,3
'Results'!D14 = vlookup ('calculations'!B13,table,3)
'Results'!D15 = vlookup ('calculations'!B14,table,3)

I might also want a macro for each cell, which would look like one line from above meta-code.

Would R1C1 method make it easier or harder?

thank you

garbo74412013-08-16T13:48:39Z

Favorite Answer

You can either directly return the result of the VLOOKUP functions in the sheet/cell specified, or enter the VLOOKUP formula in the sheet/cell specified.

Sheets("Results").[D12].Value = Application. VLookup(Sheets("Calculations"). [B11],Table,3)

Keep in mind your VLOOKUP structure requires the Table to be sorted ascending. If it is not sorted, use:

Sheets("Results").[D12].Value = Application. VLookup(Sheets("Calculations"). [B11],Table,3,False)

The second option is to use VBA to place the VLOOKUP formula in the cells:

Sheets("Results").[D12].Formula = "=VLookup('Calculations'!B11, Table,3)"

To loop through a range of cells and apply the VLOOKUP function:

For i = 12 to 15
Sheets("Results").Cells(i,"D").Value = Application. VLookup(Sheets("Calculations"). Cells(i-1,"B").Value,Table,3)
Next

2 0

?2017-01-19T02:41:14Z

0 0

?2016-11-09T01:47:42Z

Vlookup Macro

0 0

excel macro: How to fill range with formula based on values of range on different sheet?

Related Questions

Trending Questions