You need 3 double quotes instead of just 1

ActiveCell.FormulaR1C1 = "=VLOOKUP(""" & ThisSheet &
""",ClosesStock!C[-1]:C,2,FALSE)"


"David" wrote:

Hi Group,

ActiveCell.FormulaR1C1 = "=VLOOKUP(""AMAT"",ClosesStock!C[-1]:C,2,FALSE)"

Above works and is hard coded with ""AMAT"", but I am having a hard time
substituting a variable into the Formula. The Variable is ThisSheet and am
trying to get something like this to work:

ActiveCell.FormulaR1C1 = "=VLOOKUP(" & ThisSheet &
",ClosesStock!C[-1]:C,2,FALSE)"

but it fails for lack of the quote marks, it needs Text.

Thanks,
David