ExcelBanter - vlookup with variable

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - vlookup with variable (https://www.excelbanter.com/excel-programming/427158-vlookup-variable.html)

David

vlookup with variable

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

joel

vlookup with variable

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

David

vlookup with variable

Thanks Joel.

David

"joel" wrote:

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

All times are GMT +1. The time now is 02:42 PM.