Using vba for vlookup from a different file

Question

I'm trying to write a code to use vlookup where the reference is another file. The reference table is from cell a2 to a300. The lookup value is from cell G2 to end of column. The vlookup is to be done in column AA.

I found the code below on one of the solutions on this website, but have no idea how to change it to suit my file.

Sub SBEPlannerAdder()
Dim rw As Long, x As Range
Dim extwbk As Workbook, twb As Workbook

Set twb = Workbooks.Open("C:\Users\OUROBOROS\Desktop\30-5-19\vba\VBA\Gents_SW_May'19.xlsb")
Set extwbk = Workbooks.Open("C:\Users\OUROBOROS\Desktop\30-5-19\vba\VBA\1st phase stores.xlsx")
Set x = extwbk.Worksheets("Sheet1").Range("A1:C300")


    For rw = 2 To twb.Sheets("Sheet1").Cells(Rows.Count, 25).End(xlUp).Row
       twb.Sheets("Sheet1").Cells(rw, 2) = Application.VLookup(twb.Sheets("Sheet1").Cells(rw, 1).Value2, x, 2, False)
    Next rw

extwbk.Close savechanges:=False

End Sub

enter image description here

As your reference table is only a single column, are you just checking the values in G exist in the table? — SJR

SJR SJR · Accepted Answer · 2019-06-12T11:42:13

Try this. You'll need to adjust the workbook and sheet names to suit.

Not sure what output you are expecting, I have just added a text message in the output cells in AA.

Sub y()

Dim rw As Long, x As Range, v As Variant
Dim extwbk As Workbook, twb As Workbook

Set twb = ThisWorkbook 'file with lookup values (containing code)
Set extwbk = Workbooks.Open("C:\Users\OUROBOROS\Desktop\30-5-19\vba\VBA\1st phase stores.xlsx") 'file with reference table
Set x = extwbk.Worksheets("Sheet1").Range("A2:A300")

For rw = 2 To twb.Sheets("Sheet1").Cells(Rows.Count, "G").End(xlUp).Row
    v = Application.Match(twb.Sheets("Sheet1").Cells(rw, "G").Value2, x, 0)
    If IsNumeric(v) Then
        twb.Sheets("Sheet1").Cells(rw, "AA").Value = "Found" 'G is in the table
    Else
        twb.Sheets("Sheet1").Cells(rw, "AA").Value = "Not found" ''G is NOT in the table
    End If
Next rw

extwbk.Close savechanges:=False

End Sub

Using vba for vlookup from a different file

1 Answers