Excel VBA VLookup Fails

Question

I'm not a VBA expert by any means, but know enough to do the basics. I'm working on a script to help out a local animal shelter. There are four sheets that they've been manually combining into one report. All four reports contain a common variable, which I've named AnimalID. The intent is to find the AnimalID on the Rabies Renewal sheet and return an expiration date to the Report sheet (209 instances as incremented by Count). I've copied the code for one of the selections below. When I attempt to run the script, I receive the following error (through msgbox Err.Description):

Error: "Method 'VLookup' of object 'WorksheetFunction' failed"

AnimalID's are in the first column of the Rabies Renewal sheet and in column Q of the Report. Column K on the Rabies Renewal sheet contains expiration dates, which should copy to column P of the Report. I've tried formatting the AnimalID's as text and numbers but haven't had success.

Dim Count As Long
Dim AnimalID, Renewal As Range

Worksheets("Report").Activate
Range("Q:Q").NumberFormat = "@"
Worksheets("Rabies Renewal").Activate
Range("A:A").NumberFormat = "@"

Worksheets("Rabies Renewal").Activate
Set Renewal = Sheets("Rabies Renewal").Range("A:AO")

Worksheets("Report").Activate

Range("Q8").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Count = Selection.Count

For Count = 1 To Count
    AnimalID = "Q" & Count + 7

    Range("P" & Count + 7).Select
    ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Renewal, 11, False)
Next Count

I've also tried a few variants on the syntax, including the lookup value as Range(AnimalID).Value; defining the function as Application.WorksheetFunction.VLookup; table array as a range; and rangelookup as both true and false. Any suggestions would be greatly appreciated. Thank you for your help!

Answer 1

This is the expected result for a failed lookup. If have a VLOOKUP() formula in the worksheet like:

You get the #N/A error because 11 does not exist in the little table.

If you run the same thing in VBA:

Sub FailedLookup()
   Dim v As Variant
   v = WorksheetFunction.VLookup(11, Range("A:B"), 2, False)
End Sub

the #N/A does not come through, instead we see:

Answer 2

You need to Set a Range object, not just concatenate a string that looks like the cell address. However, you can use that concatenated string to help define the Range object.

Set AnimalID = Range("Q" & Count + 7)

That will set AnimalID to the Q8 cell of the Report worksheet on the first pass of the For ...Next Statement and cells further down with each subsequent pass.

Here is a wider rewrite of your procedure, removing the necessity to use the .Select and .Activate commands¹.

Dim cnt As Long
Dim AnimalID As Range, Renewal As Range  '<~~declare these PROPERLY!

With Worksheets("Rabies Renewal")
    .Range("A:A").NumberFormat = "@"
    Set Renewal = .Range("A:AO")
End If

With Worksheets("Report")
    .Range("Q:Q").NumberFormat = "@"

    For cnt = 8 To .Cells(Rows.Count, "Q").End(xlUp).Row
        Set AnimalID = .Range("Q" & cnt)
        .Range("P" & cnt) = _
            WorksheetFunction.VLookup(AnimalID, Renewal, 11, False)
    Next Count
End With

---

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}