R1C1 Vlookup with Variables to reference column numbers

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.

Note that RndRef and RndRef1 are different variables. I'll assume the Dim statement is a typo.

The problem is

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

That should be

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

That said, I need to do this sort of thing often, and the approach I take is like this.

Const FORMULATEMPLATE As String = "=VLOOKUP(RC[-3],[ExampleBook.xlsx]Wide!C1:C#,#,FALSE)"
':
ActiveCell.FormulaR1C1 = Replace(FORMULATEMPLATE, "#", RndRef1)

It was indeed a typo.

Thank you for the information, I am going to give the Const FORMULATEMPLATE route a try since me just trying to make the change you are indicating
won't let me leave the line without the error popping up at at the &","& location in the formula. I have attached the screenshot with all my primary identifiers blurred out.

If you have any other thoughts please let me know, and I'll let you know if the other route works for me!

Thank you!!

That Const FormulaTemplate route worked like a charm!

Thank you so much for your advice!