VBA - Excel : Vlookup crashes my program when no match found

Question

In my program, the user types a Zip Code and gets as an output information related to the Zip Code (province, city, district). To do this, I use the Vlookup function. So, the user :

1. Types a Zip code in the main sheet
   
2. The program search in a database (in another sheet) in which Zip Code are associated to City, Province, District.
   
3. When there is a match, it sends the result to the main pages, so the user can get a city, province, district just by typing the Zip Code. Quite simple process.

I use this code to do so :

If Range("J9").Value <> "N/A" Then 'if there is actually a zip code entered by the user (if not, it will be "N/A")
cityZip = Application.WorksheetFunction.VLookup(sMain.Range("J9").Value,
                 sZipCodes.Range("B2:E864"), 3, False)
barangayZip = Application.WorksheetFunction.VLookup(sMain.Range("J9").Value,   
                 sZipCodes.Range("B2:E864"), 2, False)
provinceZip = Application.WorksheetFunction.VLookup(sMain.Range("J9").Value,    
                 sZipCodes.Range("B2:E864"), 4, False)
sMain.Range("J7").Value = provinceZip
sMain.Range("J13").Value = cityZip
sMain.Range("J16").Value = barangayZip
Else
End If

It works perfectly when there is a Zip Code which is in my database. But if not, it crashes the execution of the program and I have an error message (like "execution error '1004', unable to read the Vlookup ...). How to modify my code to just say that if there is no match, then it should just do nothing? I don't know how to introduce this request in a Vlookup function.

Thanks in advance !

EDIT : here is my new code, after following Tim Williams suggestion :

'Using Zip Code
If Range("J9").Value <> "N/A" Then
provinceZip = Application.Lookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E907"), 4, False)

If IsError(provinceZip) = False Then
cityZip = Application.Lookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E907"), 3, False)
barangayZip = Application.Lookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E907"), 2, False)

sMain.Range("J7").Value = provinceZip
sMain.Range("J13").Value = cityZip
sMain.Range("J16").Value = barangayZip
Else
'do nothing
End If

End If

My error is on this line :

provinceZip = Application.Lookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E907"), 4, False)

=> Error 1004, invalid number of arguments

Solution 1

You should read up on VBA error handling. A source such as http://www.cpearson.com/excel/errorhandling.htm might help. That said, try the following code.

You want something like:

Public Function SafeVlookup(lookup_value, table_array, _
                        col_index, range_lookup, error_value) As Variant
    On Error Resume Next
    Err.Clear
    return_value = Application.WorksheetFunction.VLookup(lookup_value, _
                                table_array, col_index, range_lookup)
    If Err <> 0 Then
      return_value = error_value
    End If

    SafeVlookup = return_value
    On Error GoTo 0
End Function

In your code you might call it like:

cityZip = SafeVlookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E864"), 3, _
                   False, "")

The last parameter is the default value to return if the vlookup failed. So in this example it'd return an empty string.

Solution 2

I usually wrap the vlookup() with an iferror() which contains the default value.

The syntax would be as follows:

iferror(vlookup(....), <default value when lookup fails>)

You can also do something like this:

Dim result as variant
result = Application.vlookup(......)
If IsError(result) Then
  ' What to do if an error occurs
Else
  ' what you would normally do
End if

Solution 3

You changed from Vlookup to Lookup, which has less arguments. Using only 2 arguments, you should be fine: provinceZip = Application.Lookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E907") )

Comments

[guitarthrower]

This won't work at all. You can't change the arguments of the built-in VLOOKUP formula. You are using an error_value in the last VLOOKUP argument to determine if vlookup is looking for an exact match or not. Why not use the solution as @andASM provided it?