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?

CodePudding user response：

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)"