Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
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
3 Answers
- garbo7441Lv 78 years agoFavorite 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
