CODEWITHSUNDEEP
excelvba
anishmbait
anishmbait

Reputation: 31

VBA Lookup function giving incorrect number on Error

I appreciate if you can help me on below code. I'm trying to do a lookup with the VBA Lookup function, and it gives an error 1004 when the looked up value isn't available in the table array. I have tried to use "On resume Resume Next" command to skip when result an error, but instead of skipping and giving a blank result, it throws the previous value on it.

Sub x()

Dim d As String

Set src = Range("A1:A5") 'list of names needs to be searched
Set Rng = Range("D1:E5") 'table array

On Error Resume Next

For Each cell In src

d = Application.WorksheetFunction.VLookup(cell.Value, Rng, 2, 0)
cell.Offset(0, 1).Value = d

Next cell

End Sub

Upvotes: 0

Views: 209

Answers (2)

Mrblackey
Mrblackey

Reputation: 94

What happens is that when you skip an assignment which is causing an error in d = Application.WorksheetFunction.VLookup(cell.Value, Rng, 2, 0), the previous value hasn't been cleaned from the d variable.

You can try two approaches here:
1. Initialize the string in every iteration:

On Error Resume Next
For Each cell In src
    d = vbNullString
    d = Application.WorksheetFunction.VLookup(cell.Value, Rng, 2, 0)
    cell.Offset(0, 1).Value = d
Next cell

2. Skip assignment of string to cell, by defining a label, rather then Resume Next:

On Error Resume NextCell
For Each cell In src
    d = Application.WorksheetFunction.VLookup(cell.Value, Rng, 2, 0)
    cell.Offset(0, 1).Value = d
NextCell:
Next cell

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166306

If you drop the Worksheetfunction then a non-match will not throw a run-time error and you can instead test the return value using IsError() 

Sub x()
    Dim src As Range, cell As Range, Rng As Range
    Dim d as Variant  'not String, because it might need to
                      '  hold an error value if no match

    Set src = Range("A1:A5") 'list of names needs to be searched
    Set Rng = Range("D1:E5") 'table array

    For Each cell In src.Cells

        d = Application.VLookup(cell.Value, Rng, 2, 0)
        cell.Offset(0, 1).Value = IIf(IsError(d), "No Match", d)

    Next cell

End Sub

Upvotes: 1

Related Questions