CODEWITHSUNDEEP
excelvbavlookup
Phalanx
Phalanx

Reputation: 1217

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

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

Upvotes: 5

Views: 19758

Answers (4)

Dimas
Dimas

Reputation: 116

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") )

Upvotes: 1

pmyk
pmyk

Reputation: 11

SafeVlookup is a good function. I am still learning VB. I changed like this 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

Hope I can use it like this.

Upvotes: -1

AndASM
AndASM

Reputation: 10328

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.

Upvotes: 5

Ashwin Balamohan
Ashwin Balamohan

Reputation: 3332

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

Upvotes: 2

Related Questions