CODEWITHSUNDEEP
vbaexcel
user3206981
user3206981

Reputation: 49

VBA Vlookup with defined name range

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.

Upvotes: 2

Views: 1603

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626709

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)"

Upvotes: 1

Related Questions