Object Not found doing VBA Vlookup

Question

I am trying to vlookup few columns from another sheet, and I am trying to dynamically set range for the vlookup table and then copy and paste the formula down to my lookup values sheet (which works)

Any Help would be great!

I tried the code below but it does not set value in FRow or SRow.

Sub test()
    Dim FRow As Long
    Dim SRow As Long
    With Sheets("M2URPN")
        Set FRow = Sheets("M2URPN").Cells(Rows.Count, "A").End(xlUp).Row
    End With
    With Worksheets("M2URPN")
        Set SRow = .sht.Cells(sht.Rows.Count, "G").End(xlUp).Row
    End With
    If Worksheets("RECONCILE").Range("A2") Is Nothing Then
        Worksheets("RECONCILE").Range("A2").FormulaR1C1 = "NO RECORDS"
    Else
        With Worksheets("RECONCILE")
            Range("B2").Formula = "=VLOOKUP(A2,M2URPN!$A$1:$E$" & FRow & ",4,FALSE)"
            Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).FillDown
            Range("C2").Formula = "=VLOOKUP(A2,M2URPN!$A$1:$E$" & FRow & ",4,FALSE)"
            Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).FillDown
        End With
    End If
    If Worksheets("RECONCILE").Range("E2") Is Nothing Then
        Worksheets("RECONCILE").Range("E2").FormulaR1C1 = "NO RECORDS"
    Else
        With Worksheets("RECONCILE")
            Range("F2").Formula = "=VLOOKUP(E2,M2URPN!$G$1:$J$" & SRow & ",4,FALSE)"
            Range("F2:F" & Range("E" & Rows.Count).End(xlUp).Row).FillDown
            Range("G2").Formula = "=VLOOKUP(E2,M2URPN!$G$1:$J$" & SRow & ",3,FALSE)"
            Range("G2:G" & Range("E" & Rows.Count).End(xlUp).Row).FillDown
        End With
    End If

Set is only for object-type variables. You don't need Set when setting a Long — Tim Williams
Also With Worksheets("M2URPN"): .sht. Worksheet` doesn't have sht member. — JohnyL

Mithun Kumar Mithun Kumar · Accepted Answer · 2019-01-12T15:36:38

I fixed it as below:

Sub Vlookup()
    Worksheets("RECONCILE").Activate
    If Worksheets("RECONCILE").Range("A2") = "" Then
        Worksheets("RECONCILE").Range("A2").FormulaR1C1 = "NO RECORDS"
    Else
        With Worksheets("RECONCILE")
            Range("B2").Formula = "=VLOOKUP(A2,M2URPN!$A$1:$E$" & Sheets("M2URPN").Cells(Rows.Count, 1).End(xlUp).Row & ",4,FALSE)"
            Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).FillDown
            Range("C2").Formula = "=VLOOKUP(A2,M2URPN!$A$1:$E$" & Sheets("M2URPN").Cells(Rows.Count, 1).End(xlUp).Row & ",4,FALSE)"
            Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).FillDown
            Worksheets("RECONCILE").Range("B1").Value = "Amount"
            Worksheets("RECONCILE").Range("C1").Value = "Customer Account"
        End With
    End If
    If Worksheets("RECONCILE").Range("E2") = "" Then
        Worksheets("RECONCILE").Range("E2").FormulaR1C1 = "NO RECORDS"
    Else
        With Worksheets("RECONCILE")
            Range("F2").Formula = "=VLOOKUP(E2,M2URPN!$G$1:$J$" & Sheets("M2URPN").Cells(Rows.Count, 7).End(xlUp).Row & ",4,FALSE)"
            Range("F2:F" & Range("E" & Rows.Count).End(xlUp).Row).FillDown
            Range("G2").Formula = "=VLOOKUP(E2,M2URPN!$G$1:$J$" & Sheets("M2URPN").Cells(Rows.Count, 7).End(xlUp).Row & ",3,FALSE)"
            Range("G2:G" & Range("E" & Rows.Count).End(xlUp).Row).FillDown
            Worksheets("RECONCILE").Range("F1").Value = "Amount"
            Worksheets("RECONCILE").Range("G1").Value = "Customer Account"
        End With
    End If
    Worksheets("RECONCILE").Columns(2).NumberFormat = "0"
    Worksheets("RECONCILE").Columns(7).NumberFormat = "0"
    Range("A1:L1").Font.Bold = True
    For Each sht In ThisWorkbook.Worksheets
    sht.Cells.EntireColumn.AutoFit
    Next sht
End Sub

Object Not found doing VBA Vlookup

1 Answers