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

Question

In my program, the user enters an index code and receives as output information related to the postcode (province, city, district). For this, I use the Vlookup function. So user:

• Creates a zip code on the main sheet
• Search for a program in a database (on another sheet) in which a zip code is associated with a city, province, district.
• When there is a match, it sends the result to the main pages, so the user can get the city, province, district by simply typing the index code. Pretty simple process.

I use this code for this:

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 great when there is a zip code that is in my database. But if not, it leads to a crash in the program, and I get an error message (for example, "runtime error 1004", unable to read Vlookup ...). How do I change my code to just say that if there is no match, then it just has to do nothing? I do not know how to enter this query into the Vlookup function.

Thanks in advance!

EDIT: here is my new code after 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 on this line:

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

=> Error 1004, invalid number of arguments

Answer 1

You should read VBA error handling information. A source, such as http://www.cpearson.com/excel/errorhandling.htm , can help. However, try the following code.

You need 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 code, you can call it the following:

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

The last parameter is the default value to return if vlookup failed. Therefore, in this example, it will return an empty string.

Answer 2

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

The syntax will 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 

Answer 3

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

Answer 4

SafeVlookup is a good feature. I am still learning VB. I have changed so and it works for me.

  Function SafeVlookup(lookup_value, _ range_lookup, col_index, error_value) As Variant ..... return_value = Application.WorksheetFunction.vlookup(lookup_value, _ range_lookup, col_index, error_value) .... End Function 

I hope I can use it like this.