VBA - Vlookup formula - Codename of sheet for VLookup not working


How are you doing?

I have the following sub (See code and workbook below).

My issue is with line 14:
rng1.FormulaR1C1 = "=vlookup(RC[-1]," & CorrectedAgentIDName.[C1:C2].Address(External:=True, ReferenceStyle:=xlR1C1) & ",2,FALSE)"

Open in new window

This returns the following in Excel:

=VLOOKUP(A2,'Corrected ID-Name'!$C$1:$C$2,2,FALSE)

I want it to return:

=VLOOKUP(A2,'Corrected ID-Name'!A:B,2,FALSE)

So that it is looking up the value in A2 and finding that on sheet Corrected ID-Name (codename CorrectedAgentIDName) using column A:B, returning from column 2 (B) and False.

What do I need to change in the formula so that column A and B are used for the lookup table?

Thank you,
Sub VLookUpAutomation()

Dim cell As Range
Dim rng1, rng2 As Range

Application.ScreenUpdating = False

' Select Sheet or set for just active sheet or provide combo box?
Columns("B:B").Insert Shift:=xlToRight ' Insert column for vlookup formula to reside
Range("B1").Value = "VL" ' Indicate which column is vlookup, by putting in text VL

' Set range for vlookup formula and fill down in column B
Set rng1 = Range("C2", Range("C2").End(xlDown)).Offset(, -1)
    rng1.FormulaR1C1 = "=vlookup(RC[-1]," & CorrectedAgentIDName.[C1:C2].Address(External:=True, ReferenceStyle:=xlR1C1) & ",2,FALSE)"
' Test for Error (#N/A), when found, copy cell to left (, -1) and to right (, 1) then paste on sheet 2
' Re-name (Name) Sheet 2 so that user changes will not effect which page "Corrected Names are Stored"
On Error Resume Next
For Each cell In rng1.SpecialCells(xlCellTypeFormulas, xlErrors)
    If cell.Value = CVErr(xlErrNA) Then
        With CorrectedAgentIDName
            If IsError(Application.Match(cell.Offset(, -1), .Columns(1), 0)) Then
                cell.Offset(, -1).Copy .Range("A" & Rows.Count).End(xlUp)(2)
                cell.Offset(, 1).Copy .Range("B" & Rows.Count).End(xlUp)(2)
            End If
        End With
    End If
Next cell
On Error GoTo 0

' Set range to vlookup values from B2:Bn - Copy Range
' PasteSpecial Values into C2:Cn
Set rng2 = Range("B2", Range("B2").End(xlDown))
    Range("C2").PasteSpecial xlPasteValues

' Delete column B
Set rng2 = Range("B1", Range("B2").End(xlDown))

    Columns("A:B").AutoFit ' Fit Columns A and B to be visible
    Range("A2").Select ' Set cursor at Cell "A2"

Application.ScreenUpdating = True

End Sub

Open in new window

LVL 23
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:

   rng1.FormulaR1C1 = "=vlookup(RC[-1]," & CorrectedAgentIDName.[C1:C2].Address(External:=True, ReferenceStyle:=xlR1C1) & ",2,FALSE)"


   rng1.FormulaR1C1 = "=vlookup(RC[-1]," & CorrectedAgentIDName.[A:B].Address(External:=True, ReferenceStyle:=xlR1C1) & ",2,FALSE)"

redrumkevAuthor Commented:

Ding, Ding, Ding, we have a 500 point winner!

Thank you!
All Courses

From novice to tech pro — start learning today.