Search code examples
excelexcel-2007vba

Why doesn't isError( ) work with a vlookup statement in excel VBA

I'm using excel 2007 and have created a UDF that includes three vlookup() statements. The function is supposed to return the sum of all three vlookup statments. In the majority of cases, only two the vlookup() statements will return a valid value the third statement will result in an NA because the lookup value is not included in the lookup range.

I have tried to trap the error and return a zero by using:

  1. Application.WorksheetFunction.iferror(vlookup(...) ,0)

  2. A conditional that uses If iserror(vlookup()) then ...

but I can't seem to get either approach to work. If I comment out the vlookup that I know is creating the error everything works as expected.

Does anyone know why iserror(0 and iserror() don't seem to be working or perhaps an alternative approach that will work.

Update:

Here are the three vlookup function:

product2 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productA, lookuprng, offset, False), 0)

product3 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productB, lookuprng, offset, False), 0)

product4 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productC, lookuprng, offset, False), 0)
Solution

  • You can trap the error with the following:

    Sub HandleVlookupErrors()
    Dim result As Variant

    result = Application.VLookup(productA, lookuprng, offset, False)
    If IsError(result) Then result = 0

End Sub

    For a full explanation, please see Error Handling Within Worksheet Functions.