vlookup with variable

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
 
J

joel

You need 3 double quotes instead of just 1

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

David

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Worksheet function 3
Macro 3
Problem with SET & REF function 0
Vlookup in vba - how to use absolute rows not relative 1
Inability to add quotation mark around a variable by concatenation in VBA 4
Vlookup error 1
macro to copy rows that contain a cell with a certain number 3
Run-time error '1004': Application defined or Object defined error 2

Top