Databases
StackOverflow2013
Postsdbo
POVBA - Excel : Vlookup crashes my program when no match found
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA - Excel : Vlookup crashes my program when no match found
    primarykey
    data
    text
    <p>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 :</p> <ol> <li>Types a Zip code in the main sheet<br></li> <li>The program search in a database (in another sheet) in which Zip Code are associated to City, Province, District.<br> </li> <li>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.</li> </ol> <p>I use this code to do so : </p> <pre><code>If Range("J9").Value &lt;&gt; "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 </code></pre> <p>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.</p> <p>Thanks in advance ! </p> <p>EDIT : here is my new code, after following Tim Williams suggestion : </p> <pre><code>'Using Zip Code If Range("J9").Value &lt;&gt; "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 </code></pre> <p>My error is on this line : </p> <pre><code>provinceZip = Application.Lookup(sMain.Range("J9").Value, sZipCodes.Range("B2:E907"), 4, False) </code></pre> <p>=> Error 1004, invalid number of arguments</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPhalanx
    1. USPhalanx
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POVBA - Excel : Vlookup crashes my program when no match found
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POVBA - Excel : Vlookup crashes my program when no match found
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POVBA - Excel : Vlookup crashes my program when no match found
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODrop the `.WorkSheetFunction` and just use `Application.Lookup`. Then you can test the return value using `IsError()` on the first lookup
      singulars
      1. POVBA - Excel : Vlookup crashes my program when no match found
      1. USTim Williams
    2. CO@Tim is right. However, why not just put the `VLOOKUP` formulas in cells J7, J13 and J16 directly? You could also make J9 a data validation dropdown whose source range is your list of zip codes. That way users could only choose valid zip codes and you could avoid (possibly) unneeded coding.
      singulars
      1. POVBA - Excel : Vlookup crashes my program when no match found
      1. USDoug Glancy
    3. CO@TimWilliams : thank you! I found more details here (http://www.cpearson.com/excel/callingworksheetfunctionsinvba.aspx ) and tried to follow their example, but I still have a problem, different one this time (but still error 1004), when I try to run my program... I'm gonna edit my code with more details.
      singulars
      1. POVBA - Excel : Vlookup crashes my program when no match found
      1. USPhalanx
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload