Setting the table_array in a VLOOKUP in VBA excel

Question

I am trying vlookup in VBA. I am referencing to another sheet within the same workbook but i am getting #NAME? as a result.

Sub MatchNames()

l_row = Worksheets("Lookup").Cells(Rows.Count, 1).End(xlUp).Row
lookup_range = Worksheets("Lookup").Range("A2:E" & l_row)

Final_row = Cells(Rows.Count, 6).End(xlUp).Row

    With Range("BG2:BG" & Final_row)
        .FormulaR1C1 = "=vlookup(RC[-52],lookup_range, 2, False)"

    End With

End Sub

@Ageonix - I don't think that linked answer is a duplicate. In that linked answer the problem was using the worksheet name as the external range; here it is using a declared var as a named range. — user4039065
– user4039065, Commented Feb 18, 2016 at 6:45

score 1 · Accepted Answer · 2016-02-18 06:42:25Z

1

Try,

l_row = Worksheets("Lookup").Cells(Rows.Count, 1).End(xlUp).Row
Set lookup_range = Worksheets("Lookup").Range("A2:E" & l_row)  '<~~one edit here

With Range("BG2:BG" & Final_row)
    .FormulaR1C1 = "=vlookup(RC[-52]," & lookup_range.address(referencestyle:=xlR1C1, external:=true) & ", 2, False)"  '<~~another edit here
End With

The Range.Address property can return the entire path and sheet name. Giving too much is not detrimental.

This might be a better method.

    Dim l_row As Long, f_row As Long, sLookup_Range As String

    With Worksheets("Lookup")
        l_row = .Cells(Rows.Count, "A").End(xlUp).Row
        sLookup_Range = .Range("A2:E" & l_row).Address(ReferenceStyle:=xlR1C1, external:=True)
    End With

    With Worksheets("Sheet1")
        f_row = .Cells(Rows.Count, "G").End(xlUp).Row
        With .Range("BG2:BG" & f_row)
            .FormulaR1C1 = "=vlookup(RC[-52]," & sLookup_Range & ", 2, False)"
        End With
    End With

edited Feb 18, 2016 at 6:42

answered Feb 18, 2016 at 6:28

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Haaris Ali Over a year ago

Thank you everyone. @Jeeped I used your solution and it worked perfectly. Out of curiosity, do you know what was the problem in my code?

Collectives™ on Stack Overflow

Setting the table_array in a VLOOKUP in VBA excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related