Formula with dynamic reference to a specific cell-CodePudding

in the following expression I would like to set B2 dynamic pointing to the right cell where I am inserting the formula:

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

How can I achive that?

CodePudding user response：

If dynamic means to reference "B" on the same row with the cell receiving the formula, please try the next way:

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

And VBA uses comma as list separator, Independent of localization you should use comma...

Edited: In order to use in formula the cell to the left of the one where the formula is written, plese try the next way:

WS1.cells(4   j - 1, 3   ((i - 1) * 9)).Formula = _
    "=VLOOKUP(" & WS1.cells(4   j - 1, 3   ((i - 1) * 9) - 1).Address(0, 0) & ",WS2!B:C,2,FALSE)"