Vlookup Error Handler Issues

[Nicole17]

Hello!

I created an excel macro that looks up last names in a table on another worksheet using vlookup. If a last name is found, the number 15 is placed in a column on the first worksheet. If the name is not found (which is common), I want a 4 to be placed there instead. I tried to deal with this using an error handler, but instead of returning 4, the handler just puts a 15 next to every term whether it appears in the vlookup range or not! Did I enter something incorrectly? I'm a VBA beginner so I feel like I'm missing something really obvious.


Code I'm Using:

Sub LookUpAndCopy()

On Error GoTo ErrorHandler:

Dim LastName As Range
Dim b As Long

For b = 3 To 12
    Set LastName = Sheets("Testing").Range("B" & b)
    Sal = Application.WorksheetFunction.VLookup(LastName, Sheets("Admin").Range("AF3:AG12"), 2, False)
    Sheets("Testing").Range("K" & b).Value = Sal
Next b

ErrorHandler:
    If Err.Number = 1004 Then
        Sheets("Testing").Range("K" & b).Value = "4"
        Resume Next
    End If

End Sub

[6StringJazzer]

Welcome to the Forum Nicole17!

The code Resume Next causes execution to resume at the line of code following the one that caused the error. In your case, that line is the assignment of Sal to the Kb cell. So when you resume after the error, that line of code wipes out the value that was put there by your error handler.

To fix it, have your error handler update Sal instead of putting the value in the cell.

Sub LookUpAndCopy()

On Error GoTo ErrorHandler:

Dim LastName As Range
Dim b As Long

For b = 3 To 12
    Set LastName = Sheets("Testing").Range("B" & b)
    Sal = Application.WorksheetFunction.VLookup(LastName, Sheets("Admin").Range("AF3:AG12"), 2, False)
    Sheets("Testing").Range("K" & b).Value = Sal
Next b

ErrorHandler:
    If Err.Number = 1004 Then
        Sal = 4
        Resume Next
    End If

End Sub

I have made the minimal changes necessary to get your code to work. Here is another suggestion. Putting a Resume Next in an error handler results in what we old timers call "spaghetti code." Looking at the code you can't be sure where execution is going after that Resume Next (an experienced VBA programmer will know exactly which lines of code can raise a 1004 error, but it's better to be obvious). Here is another way to do this. This way you know exactly where the code is flowing under what conditions.

Sub LookUpAndCopy()

On Error Resume Next

Dim LastName As Range
Dim b As Long

For b = 3 To 12
    Set LastName = Sheets("Testing").Range("B" & b)
    Sal = Application.WorksheetFunction.VLookup(LastName, Sheets("Admin").Range("AF3:AG12"), 2, False)
    If Err.Number = 1004 Then
        Sal = 4
        Err.Clear
    End If
    Sheets("Testing").Range("K" & b).Value = Sal
Next b

End Sub

[Norie]

You could do this without On Error...

Sub LookUpAndCopy()

Dim LastName As Range
Dim b As Long
Dim Sal As Variant

    For b = 3 To 12
        Set LastName = Sheets("Testing").Range("B" & b)
        Sal = Application.VLookup(LastName, Sheets("Admin").Range("AF3:AG12"), 2, False)
        If IsError(Sal) Then
            Sheets("Testing").Range("K" & b).Value = 4
        Else
            Sheets("Testing").Range("K" & b).Value = Sal
        End If
    Next b

End Sub