I have problem with Vlookup in different workbook.
I took code from below link as a baseline, but somehow I cannot make it work.
Any ideas of what I did wrong?
I have 2 files.
- worksheet "PRJ"
- value from column "Z" needs to be looked up in File 2
- worksheet "Page 1"
- the same values are in column "A" and
- value that is searched for is in column "F"
Value after being looked up needs to be paste in File 1, column "AA" in corresponding row
Formula that would work here if I hand't try macro is:
Macro I have so far:
Private Sub VLookup2() On Error Resume Next Dim myLookupValue As String Dim myFirstColumn As Long Dim myLastColumn As Long Dim myColumnIndex As Long Dim myFirstRow As Long Dim myLastRow As Long Dim myVLookupResult As Long Dim myTableArray As Range Set wb1 = Workbooks("File 1.xlsb").Sheets("PRJ") myLookupValue = "wb1.Z7" myFirstColumn = 1 myLastColumn = 6 myColumnIndex = 6 myFirstRow = 2 myLastRow = 500 With Workbooks("File 2.xlsx").Worksheets("Page 1") Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) End With myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False) End Sub