excel - Skip over function error and continue with next check (VLOOKUP)

Question

I am running a basic VLOOKUP to get an employees email address.

However, when the lookup value enters an employees name that doesn't exist in the array, it throws an error.

I added an "On Error Resume Next" but this just caused the remainder of the email addresses to be the last email address encountered, instead of continuing the search and using the current employee as the search variable.

What I want is when the search variable ProjectManName doesn't exist in the array, to just skip over that line, and continue the search as normal. I will then go and fill in the empty email addresses manually.

Dim myLookupValue As Range
Dim strResult As String
Dim lngLastRow As Long
Dim lngLoop As Long
Dim ProjectManName As String

Set myLookupValue = Worksheets("Employees").Range("A1", Worksheets("Employees").Range("B1").End(xlDown))

With Worksheets("Project Summary")

    lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    For lngLoop = 2 To lngLastRow
        On Error Resume Next
       ProjectManName = .Cells(lngLoop, 1).Value
       strResult = Application.WorksheetFunction.VLookup(ProjectManName, myLookupValue, 2, False)
       Range("K" & lngLoop).Value = strResult
       ActiveCell.Offset(1, 0).Select
    Next

End With

Also, for the myLookupValue, do I need the second instance of Worksheets("Employees")? When I didn't do this it also gave an error because the second cell in the range looked at the current worksheet instead of the Employees worksheet.

Lastly, how can I replace "K" in Range("K" & lngLoop).Value to be the first empty column?

Answer 1

when the lookup value enters an employees name that doesn't exist in the array, it throws an error.

That's entirely by design. Application.WorksheetFunction functions are early-bound and raise errors instead of returning them, which is completely idiomatic VB behavior.

Looks like you want the "Excel worksheet" behavior, where a worksheet function that errors out will return a Variant/Error value that the cell displays as #N/A: that Variant/Error value makes IsError return True, and can only be legally compared to other error values, e.g. CVErr(xlErrNa).

Like many COM types, the Excel.Application interface is extensible, meaning members can be added to it at run-time. Turns out, it's effectively extended with members of the WorksheetFunction interface, so Application.VLookup not only compiles perfectly fine (as does Application.AnythingWhatsoever), it's a late-bound implementation that behaves exactly like the worksheet function does when invoked by a worksheet cell: it returns a Variant/Error value rather than raising a standard, idiomatic run-time error... assuming you get all the parameters right (late-bound calls don't get IntelliSense/autocomplete), because if you make a typo (Option Explicit can't save you) or get the parameters wrong, expect error 438 or 1004 to be raised.

But you can't capture the return value in a String - that'll be a type mismatch error when the lookup yields an Error value (you can't coerce that type into anything other than a Variant).

Dim lookupResult As Variant
lookupResult = Application.VLookup(ProjectManName, myLookupValue, 2, False)
If Not IsError(lookupResult) Then
    strResult = CStr(lookupResult)
    '...
''Else
''    'lookup failed
End If

That said the early-bound version should generally be preferred, be it only for IntelliSense. On Error Resume Next used correctly can be helpful here - simply pull the lookup into its own scope:

For lngLoop = 2 To lngLastRow
    ProjectManName = .Cells(lngLoop, 1).Value
    [ActiveSheet.]Range("K" & lngLoop).Value = GetProjectManager(ProjectManName)
    'ActiveCell.Offset(1, 0).Select '<~ why?
Next

Private Function GetProjectManager(ByVal name As String) As String
     Dim source As Range
     With Worksheets("Employees")
         On Error Resume Next
         GetProjectManager = Application.WorksheetFunction.VLookup(name, .Range("A1", .Range("B1").End(xlDown)), 2, False)
         On Error GoTo 0
     End With
End Function

As for myLookupValue (bad name: should be myLookupRange or lookupSource or lookupTable - "lookup value" is generally understood/read as being the value you're looking for) - you absolutely do need a reference to the Employees sheet (unqualified Range calls are a very good recipe for error 1004) - that doesn't mean you need to dereference that object from the Worksheets collection twice - as shown above... note that by moving the lookup into its own scope, we also remove the need for the caller to even need to care about the lookup source table.