vlookup with variable

  • Thread starter Thread starter David
  • Start date Start date
D

David

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
 
You need 3 double quotes instead of just 1

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

David

joel said:
You need 3 double quotes instead of just 1

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


David said:
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
Click to expand...
Click to expand...
 
Back
Top