VBA Vloopup using 2 different workbooks

Question

I have to get some information from 1 Workbook to another by using a Vlookup. The reason is that I got 75 cells that need to be filled with prices from another workbook but there will not always be 75 prices in the other workbook.

Sub Update()

Dim wb1 As Workbook, wb2 As Workbook

Set wb1 = ActiveWorkbook

' Open Workbook "Verzamelstaat" '
rowl = ActiveCell.Row
linkl = Cells(rowl, 16).Value
Application.Workbooks.Open (linkl)
Filenamel = Mid(linkl, InStrRev(linkl, "\") + 1, Len(linkl))
Set wb2 = Workbooks(Filenamel)

' Copy data to other workbook '

wb1.Sheets(1).Range("AY4").Value = Application.VLookup(wb1.Sheets(1).Range("AY2").Value, wb2.sheets(1).Range("A:A"), 2, False)

' Close Workbook "Verzamelstaat" '
Workbooks(Filenamel).Close savechanges:=False

End Sub

The problem I have is that wb1.Sheets(1).Range("AY4") ends up as #REF!.

The value of AY2 = 001 and the other workbook has 001 to 075 in column A.

I think the answer is simple but I just don't see it....

Answer 1

Change the second parameter of your VLookUp function that it refers to both A and B columns:

... = Application.VLookup(wb1.Sheets(1).Range("AY2").Value, wb2.sheets(1).Range("A:B"), 2, False)