CODEWITHSUNDEEP
vbaexcelerror-handlingvlookup
Swolerosis
Swolerosis

Reputation: 93

vlookup error handling, error 2042

I'm trying to get a Vlookup working in VBA, and I have this code:

Dim Result As Variant

    For i = 2 To lastrow
        Result = Application.VLookup(Template.Range("A" & i).Value2, EDM.Range("B:BP"), 5, False)
        If IsError(Result) Then
            Template.Range("E" & i).Value2 = ""
        Else
            Template.Range("E" & i).Value2 = Result
        End If
    Next i

For some reason every cell is coming up blank. If I manually type the formula in a cell with a match it works fine. I'm guessing it has something to do with the error handling, maybe? The goal is to look up the value in Template, column A against EDM column B and return EDM Column G.

Upvotes: 0

Views: 1857

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

You are right, it is related to your Error Handling, you need to trap the VLookup error a bit differently, by using If IsError(Application.VLookup...).

Note: in your post you wrote you are looking to compare "against EDM column B", in your code you wrote EDM.Range("B:BP"), so which one is it?

Try the code below :

Dim Result As Variant

For i = 2 To lastrow
    If IsError(Application.VLookup(Template.Range("A" & i).Value2, EDM.Range("B:BP"), 6, False)) Then
        Template.Range("E" & i).Value2 = ""
    Else
        Result = Application.VLookup(Template.Range("A" & i).Value2, EDM.Range("B:BP"), 6, False)
        Template.Range("E" & i).Value2 = Result
    End If
Next i

Upvotes: 1

Related Questions