VBA Vlookup with defined name range

Question

I am having 2 workbooks (Lookup and Destination) and trying to retrieve values from a defined name range in the Lookup workbook.

 ......
 Set rng = Range(Cells(15, 3), Cells(863, 12)) 'C15:L863
 wbkLookup.Names.Add Name:="LookupAreaDK", RefersTo:=rng
 ......
 colDest.Formula = "=VLOOKUP(" & colLookup.Address(False, False) & ", LookupAreaDK ,10,0)"

But I've got #NAME? as returned values. What is wrong with the syntax? Could anyone help? Thanks.

What is colLookup.Address(False, False)? And please check the ": they must be doubled in the macro like colDest.Formula = "=VLOOKUP(""" & colLookup.Address(False, False) & """, LookupAreaDK ,10,0)" — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Feb 17, 2015 at 14:27
colLookup.Address(False, False) is currently "AG8". Could you tell me what you mean by "doubled in the macro"? — user3206981
– user3206981, Commented Feb 17, 2015 at 14:30
formula shown in Excel file with triple quotes of colLookup.Address is: VLOOKUP(" & colLookup.Address(False, False) & "; LookupAreaDK;10;0); while the double quotes give: =VLOOKUP("AG8"; LookupAreaDK;10;0) — user3206981
– user3206981, Commented Feb 17, 2015 at 14:37
Ah, I see now. No, you do not need to "escape" the quotation marks here, but please check that the colLookup.Address(False, False) is working. I tested with Range("E1").Formula = "=VLOOKUP(" & "D1" & ", LookupAreaDK ,3,0)", and it worked alright. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Feb 17, 2015 at 14:39
Thanks, I've got to work a bit more on the colLookup.Address(False, False) — user3206981
– user3206981, Commented Feb 17, 2015 at 14:47

Wiktor Stribiżew · Accepted Answer · 2015-02-17 15:06:15Z

1

So, you need to assign the address to the colLookup, or create another variable. Then, this works:

Dim colLookup As String
colLookup = Cells(1, 4).Address
colDest.Formula = "=VLOOKUP(" & colLookup & ", LookupAreaDK ,3,0)"

answered Feb 17, 2015 at 15:06

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

VBA Vlookup with defined name range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related