Using a set variable in a Vlookup

Question

I'm trying to use the following formula in a cell where Name is a defined cell. When I run 'MsgBox Name.Address' it gives me the correct cell, but when I run the formula I get

run-time error '1004'

Worksheets("E'ee Details").Range("N1").FormulaR1C1 = "=VLOOKUP(" & Name.Address & ",EmployeeBank!C[-13]:C[-10],4,FALSE)"

The code also correctly places in the value of the variable if I remove .address

Worksheets("E'ee Details").Range("N1").FormulaR1C1 = "=VLOOKUP(" & Name & ",EmployeeBank!C[-13]:C[-10],4,FALSE)"

This gives me =VLOOKUP(J Bob,EmployeeBank!A:D,4,FALSE) in the cell. This would work but I can't put quotation marks around the name as these are already in place in the vba code. I tried doing multiple quotation marks and also char(34) but couldn't get it to work.

For future reference, Find (with Offset) is a much better alternative in VBA — CallumDA
– CallumDA, Commented Jan 4, 2018 at 13:18

user8753746 · Accepted Answer · 2018-01-04 13:18:16Z

0

You need to use an additional double quotation.

answered Jan 4, 2018 at 13:18

user8753746

Sign up to request clarification or add additional context in comments.

Comments

jufg · Accepted Answer · 2018-01-04 13:17:29Z

0

Sorted it, I was putting in the wrong number of quotation marks!!!!

Worksheets("EeeDetails").Range("N1").FormulaR1C1 = "=vlookup(""" & NewName & """,EmployeeBank!C[-13]:C[-10],4,FALSE)"

answered Jan 4, 2018 at 13:17

jufg

1631 silver badge13 bronze badges

Collectives™ on Stack Overflow

Using a set variable in a Vlookup

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related