Hello

I am having some issues with using variables in FormulaR1C1 format and would appreciate any assistance.

This formula works perfectly for me:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],[ExampleBook.xlsx]Wide!C1:C5,5,FALSE)"

I am trying to have the C5 and 5 be generated using a variable depending on the value from another cell, so I create this:

Dim RndRef As Long
RndRef1 = Range("$L$10").Value

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],[ExampleBook.xlsx]Wide!C1:C"&RndRef1&",&RndRef1,FALSE)"

Which gives me a "Compile error: Expected: end of statement". I have tried utilizing many suggestions I have seen while trying to fix the error, and they all show the same error.

I am primarily trying to cut back on copying all of my code and adjusting to generate 8 separate files that I am pulling data in from a completely separate file. I have all my code created with the exception of this one error, any help would be greatly appreciated.