Search code examples
excelvbaformulacellvlookup

Formula VLOOKUP with dynamic lookup value

I want to let the lookup vale of a vlookup dynamic with the loop counter, I tried:

WS1.Cells(4 + j - 1, 3 + ((i - 1) * 9)).Formula = "=VLOOKUP(" & WS1.Cells(4 + j - 1, 4 + ((i - 1) * 9)) & ",WS2NAME!B:C,2,FALSE)"

I though that " & WS1.Cells(4 + j - 1, 4 + ((i - 1) * 9)) & " would return the cell name, (eg B3, D5, ...) but it returns blank when I run the macro in excel.

Any suggestions?

Solution

  • Using R1C1 referencing would be easier here:

    WS1.Cells(4 + j - 1, 3 + ((i - 1) * 9)).FormulaR1C1 = "=VLOOKUP(RC[1],WS2NAME!C2:C3,2,FALSE)"