Worksheet function

[David]

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.

 

[JulieD]

Hi David

try

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

Cheers
JulieD

 

[David]

That got it. Thanks.

-----Original Message-----
Hi David

try

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

Cheers
JulieD

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.

.

 

[JulieD]

Hi David

you're welcome and thanks for the feedback.

Cheers
JulieD

That got it. Thanks.

-----Original Message-----
Hi David

try

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

Cheers
JulieD

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.

.