CODEWITHSUNDEEP
excelexcel-2007vba
Mutuelinvestor
Mutuelinvestor

Reputation: 3538

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)

Upvotes: 2

Views: 23836

Answers (3)

Andy
Andy

Reputation: 1

You may consider to write a vlookup function with error handling lines:

Public Function v_lookup(lookup_value As String, table_array As Range, col_index_num As Integer, range_lookup As Boolean) As String

Dim result As Variant
result = Application.VLookup(lookup_value, table_array, col_index_num, range_lookup)

If IsError(result) Then result = ""
v_lookup = result

End Function

Upvotes: 0

Roy
Roy

Reputation: 1

Dim Res as Variant
Res = Application.WorksheetFunction.VLookup(Vndr, Range("A:a"), 1, False) 'Where Vndr is some unknown
If Res = Vndr then 
    do xyz
Else
    do 123
endif

Upvotes: -2

Jon Crowell
Jon Crowell

Reputation: 22338

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.

Upvotes: 6

Related Questions