Excel VBA "Method 'VLookup' of object 'WorksheetFunction' failed"

Question

This is a follow-up post to an earlier post. This issue is resolved due to some great advice that was offered.

I'm helping a local animal shelter by developing a script to combine data from four worksheets into an overall report. All four sheets contain a common variable, which I've named AnimalID. AnimalID is located in column A on the Rabies Renewal and Rabies Jurisdiction sheets and is sorted from min to max. My previous question required locating the AnimalID on the Rabies Renewal sheet and using VLookup to copy an expiration date on the Report sheet. For reference, the dates are populated at the start of the report, at cell Q8 to bottom. I am trying to use (or so I think) the same approach to populate owner information on the Report sheet by finding the AnimalID on the Rabies Jurisdiction sheet. The owner information should be populated starting around cell A140 (previous cells are already populated), hence why I include two variables to mark position. When I attempt to run the script, I receive the following error when it reaches any of the commented VLookup lines.

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

I'm not sure if the A## value will populate properly but I think the others should work just fine. Thoughts and suggestions would be appreciated. Thanks!

Dim Ct As Long, lMaxRows As Long
Dim AnimalID As Range, Jurisdiction As Range, Renewal As Range

Worksheets("Rabies Renewal").Activate
Range("A:A").NumberFormat = "@"
Set Renewal = Sheets("Rabies Renewal").Range("A:K")

Worksheets("Rabies Jurisdiction").Activate
Range("A:AI").NumberFormat = "@"
Set Jurisdiction = Sheets("Rabies Jurisdiction").Range("A:AI")

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

    'Backfill owner information based on animal ID
        lMaxRows = Cells(Rows.Count, "I").End(xlUp).Row
        Range("L" & lMaxRows + 1).Select
        Range(ActiveCell, ActiveCell.End(xlDown)).Select
        Ct = Selection.Count

        For Ct = 1 To Ct
            Set AnimalID = Range("Q" & Ct + lMaxRows)

            'Range("A" & Ct + lMaxRows).Select
            'ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Jurisdiction, 19 & ", " & 18, False)
            'Range("C" & Ct + lMaxRows).Select
            'ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Jurisdiction, 27, False)
            'Range("E" & Ct + lMaxRows).Select
            'ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Jurisdiction, 32, False)
            'Range("F" & Ct + lMaxRows).Select
            'ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Jurisdiction, 33, False)
            'Range("G" & Ct + lMaxRows).Select
            'ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Jurisdiction, 34, False)
            'Range("I" & Ct + lMaxRows).Select
            'ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Jurisdiction, 16, False)
            'Range("J" & Ct + lMaxRows).Select
            'ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Jurisdiction, 8, False)
            'Range("K" & Ct + lMaxRows).Select
            'ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Jurisdiction, 11, False)

            On Error Resume Next
        Next Ct

    'Backfill rabies due dates based on animal ID
        Range("Q8").Select
        Range(ActiveCell, ActiveCell.End(xlDown)).Select
        Ct = Selection.Count

        For Ct = 1 To Ct
            Set AnimalID = Range("Q" & Ct + 7)

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

            On Error Resume Next
        Next Ct

    MsgBox "Completed"

Unknown Unknown · Accepted Answer · 2016-01-10T22:51:23

Without sample data (which understandably would be hard to include dow to 140+ rows) I had to make some assumptions but I believe that I have covered teh Report ranges correctly. Step through this with repeated taps to the F8 key. You can pause tapping and look at the worksheet to see if the formulas are being delivered a) correctly and b) to the correct ranges.

    Dim AnimalID As Range, Jurisdiction As Range, Renewal As Range

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

    With Worksheets("Rabies Jurisdiction")
        .Range("A:AI").NumberFormat = "@"
        Set Jurisdiction = .Range("A:AI")
    End With

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

        'Backfill owner information based on animal ID
        With .Range(.Cells(.Cells(Rows.Count, "I").End(xlUp).Row + 1, "L"), _
                    .Cells(.Cells(Rows.Count, "I").End(xlUp).Row + 1, "L").End(xlDown)).Offset(0, -11)
            'should be at A:A parallel to the desired L:L range here
            'A:A - column 19
            .Offset(0, 0).Formula = _
              "=IFERROR(VLOOKUP($Q" & .Rows(1).Row & ", " & Jurisdiction.Address(0, 0, external:=True) & ", 19, FALSE), """")"
            .Cells = .Value
            'C:C - column 27
            .Offset(0, 2).Formula = _
              "=IFERROR(VLOOKUP($Q" & .Rows(1).Row & ", " & Jurisdiction.Address(0, 0, external:=True) & ", 27, FALSE), """")"
            .Cells = .Value
            'E:E - column 32
            .Offset(0, 4).Formula = _
              "=IFERROR(VLOOKUP($Q" & .Rows(1).Row & ", " & Jurisdiction.Address(0, 0, external:=True) & ", 32, FALSE), """")"
            .Cells = .Value
            'F:F - column 33
            .Offset(0, 5).Formula = _
              "=IFERROR(VLOOKUP($Q" & .Rows(1).Row & ", " & Jurisdiction.Address(0, 0, external:=True) & ", 33, FALSE), """")"
            .Cells = .Value
            'G:G - column 34
            .Offset(0, 6).Formula = _
              "=IFERROR(VLOOKUP($Q" & .Rows(1).Row & ", " & Jurisdiction.Address(0, 0, external:=True) & ", 34, FALSE), """")"
            .Cells = .Value
            'I:I - column 16
            .Offset(0, 6).Formula = _
              "=IFERROR(VLOOKUP($Q" & .Rows(1).Row & ", " & Jurisdiction.Address(0, 0, external:=True) & ", 16, FALSE), """")"
            .Cells = .Value
            'J:J - column 8
            .Offset(0, 6).Formula = _
              "=IFERROR(VLOOKUP($Q" & .Rows(1).Row & ", " & Jurisdiction.Address(0, 0, external:=True) & ", 8, FALSE), """")"
            .Cells = .Value
            'K:K - column 11
            .Offset(0, 6).Formula = _
              "=IFERROR(VLOOKUP($Q" & .Rows(1).Row & ", " & Jurisdiction.Address(0, 0, external:=True) & ", 11, FALSE), """")"
            .Cells = .Value
        End With

        'Backfill rabies due dates based on animal ID
        .Range("Q8").Select
        With .Range("P8", .Range("Q8").End(xlDown).Offset(0, -1))
            .Formula = "=IFERROR(VLOOKUP(Q8, " & Renewal.Address(0, 0, external:=True) & ", 11, FALSE), """")"
            .Cells = .Value
        End With
    End With

    MsgBox "Completed"

This method avoids looping through the cells by bulk loading them into the entire destination ranges at once. They will adjust to the new rows just as if you were looping them. The formulas are reverted to their values after providing the calculated values.

Excel VBA "Method 'VLookup' of object 'WorksheetFunction' failed"

1 Answers