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

Posted on 2011-03-17
Last Modified: 2012-05-11

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

Question by:redrumkev
LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 500 total points
ID: 35161237

   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)"

LVL 23

Author Comment

ID: 35162067

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

Thank you!

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now