Hi David

try

ActiveCell.FormulaR1C1 ="=VLOOKUP(""" & ThisSheet &
""",ClosesStock!C[-6]:C[-5],2,False)"

Cheers
JulieD


"David" wrote in message
...
Hi Folks. I am tryig to do a VLookUp from code. I am
using a variable to capture the lookup value, which is
the name of the worksheet. ThisSheet is the variable name
and it might be "AA", as an example. If I try putting a
formula on the worksheet to capture a value on another
sheet in the workbook by using:

ActiveCell.FormulaR1C1 = "=VLOOKUP(" & (ThisSheet) & " ,
ClosesStock!C[-6]:C[-5],2,False)"

I lose the quote marks around the lookup value and get a
#NAME? error and do not get a value to capture.

Any suggestions would be appreciated, including using
Application.Worksheet function or modifying the above
formula. Thanks folks.