Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB error '1004' Unable to get VLookup property to work right
I'd use:
dim Res as variant 'could be an error res = application.vlookup(location, sheets("City").range("a2:b3", 2, false) with Worksheets("Master") if iserror(res) then .range("V3").value = "Missing" else .range("V3").value = res end if end with ========== Saved from a previous post: There is a difference in the way application.vlookup() and worksheetfunction.vlookup() (or application.worksheetfunction.vlookup()) behave. Application.vlookup returns an error that you can check: dim Res as variant 'could return an error res = application.vlookup(....) if iserror(res) then msgbox "no match" else msgbox res end if Application.worksheetfunction.vlookup raises a trappable error that you have to catch: dim res as variant on error resume next res = application.worksheetfunction.vlookup(...) if err.number < 0 then msgbox "no match" else msgbox res end if on error goto 0 (application.match() and application.worksheetfunction.match() behave the same way.) Personally, I find using the application.vlookup() syntax easier to read. But it's personal preference. NFL wrote: I'm getting a run-time error 1004 and don't know what is missing. I have 2 columns in the City worksheet as shown below. Thank you! A B Location City 1 Springfield 2 Dallas 3 etc. etc.. I want the Master worksheet to look for a city/town in the City worksheet and place that value in V3. dim Location as string ' number used to look for city Worksheets("Master").Activate With ActiveSheet .Range("V3") = Application.WorksheetFunction.VLookup(Location, Sheets("City").Range("A2:B3"), 2, False) End With -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error 1004 Unable to set the top property of the picture c | Excel Discussion (Misc queries) | |||
VB error '1004' Unable to get VLookup property to work right | Excel Programming | |||
Error 1004 - unable to set the FormulaArray property of the Range | Excel Programming | |||
Error 1004: Unable to get the axis property | Charts and Charting in Excel | |||
Run-time Error '1004' Unable to get the findnext property... | Excel Programming |