Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem w/ Match prop vs. Match method
Wishing all a happy new year ...
I get a runtime error with this piece of code: junk = Application.WorksheetFunction.VLookup("Min", table, Application.WorksheetFunction.Match("Gorge", top, 0)) junk is variant, table and top are ranges. The error is: "Unable to get the Match Property of the WS function" If I replace the ref to App.WSF.Match with an integer, it works fine. Thoughts? Thanks, Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem w/ Match prop vs. Match method
Try dropping the .worksheetfunction from your code:
with application junk = .VLookup("Min", table, .Match("Gorge", top, 0)) end with if iserror(Junk) then msgbox "Hey, there wasn't a match" else msgbox junk end if ========= If you want to keep the .worksheetfunction, you'll have to see if there was a runtime error. with application.worksheetfunction on error resume next junk = .VLookup("Min", table, .Match("Gorge", top, 0)) if err.number < 0 then msgbox "Hey, there wasn't a match" err.clear else msgbox junk end if on error go to 0 end with === By using the with/end with stuff, I could save some typing. George Raft wrote: Wishing all a happy new year ... I get a runtime error with this piece of code: junk = Application.WorksheetFunction.VLookup("Min", table, Application.WorksheetFunction.Match("Gorge", top, 0)) junk is variant, table and top are ranges. The error is: "Unable to get the Match Property of the WS function" If I replace the ref to App.WSF.Match with an integer, it works fine. Thoughts? Thanks, Tony -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem w/ Match prop vs. Match method
Thanks Dave,
It works and it's more elegant - I'm learning slowly, but I'm learning. But why does it work? Or more precisely, why didn't my clumsier version work? Is there something fundamental I'm missing? Tony Dave Peterson wrote in message ... Try dropping the .worksheetfunction from your code: with application junk = .VLookup("Min", table, .Match("Gorge", top, 0)) end with if iserror(Junk) then msgbox "Hey, there wasn't a match" else msgbox junk end if ========= If you want to keep the .worksheetfunction, you'll have to see if there was a runtime error. with application.worksheetfunction on error resume next junk = .VLookup("Min", table, .Match("Gorge", top, 0)) if err.number < 0 then msgbox "Hey, there wasn't a match" err.clear else msgbox junk end if on error go to 0 end with === By using the with/end with stuff, I could save some typing. George Raft wrote: Wishing all a happy new year ... I get a runtime error with this piece of code: junk = Application.WorksheetFunction.VLookup("Min", table, Application.WorksheetFunction.Match("Gorge", top, 0)) junk is variant, table and top are ranges. The error is: "Unable to get the Match Property of the WS function" If I replace the ref to App.WSF.Match with an integer, it works fine. Thoughts? Thanks, Tony -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem w/ Match prop vs. Match method
Application.worksheetfunction.match() causes a runtime error. About the only
thing you can do is to turn off the error checking and then check for yourself. application.match() returns an error that you can check with iserror(). It's just one of the vagaries of excel/vba. I find the application.match() (and application.vlookup()), prettier???. But that's just a personal choice. George Raft wrote: Thanks Dave, It works and it's more elegant - I'm learning slowly, but I'm learning. But why does it work? Or more precisely, why didn't my clumsier version work? Is there something fundamental I'm missing? Tony Dave Peterson wrote in message ... Try dropping the .worksheetfunction from your code: with application junk = .VLookup("Min", table, .Match("Gorge", top, 0)) end with if iserror(Junk) then msgbox "Hey, there wasn't a match" else msgbox junk end if ========= If you want to keep the .worksheetfunction, you'll have to see if there was a runtime error. with application.worksheetfunction on error resume next junk = .VLookup("Min", table, .Match("Gorge", top, 0)) if err.number < 0 then msgbox "Hey, there wasn't a match" err.clear else msgbox junk end if on error go to 0 end with === By using the with/end with stuff, I could save some typing. George Raft wrote: Wishing all a happy new year ... I get a runtime error with this piece of code: junk = Application.WorksheetFunction.VLookup("Min", table, Application.WorksheetFunction.Match("Gorge", top, 0)) junk is variant, table and top are ranges. The error is: "Unable to get the Match Property of the WS function" If I replace the ref to App.WSF.Match with an integer, it works fine. Thoughts? Thanks, Tony -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem w/ Match prop vs. Match method
Thanks again Dave, that helps.
Tony Dave Peterson wrote in message ... Application.worksheetfunction.match() causes a runtime error. About the only thing you can do is to turn off the error checking and then check for yourself. application.match() returns an error that you can check with iserror(). It's just one of the vagaries of excel/vba. I find the application.match() (and application.vlookup()), prettier???. But that's just a personal choice. George Raft wrote: Thanks Dave, It works and it's more elegant - I'm learning slowly, but I'm learning. But why does it work? Or more precisely, why didn't my clumsier version work? Is there something fundamental I'm missing? Tony Dave Peterson wrote in message ... Try dropping the .worksheetfunction from your code: with application junk = .VLookup("Min", table, .Match("Gorge", top, 0)) end with if iserror(Junk) then msgbox "Hey, there wasn't a match" else msgbox junk end if ========= If you want to keep the .worksheetfunction, you'll have to see if there was a runtime error. with application.worksheetfunction on error resume next junk = .VLookup("Min", table, .Match("Gorge", top, 0)) if err.number < 0 then msgbox "Hey, there wasn't a match" err.clear else msgbox junk end if on error go to 0 end with === By using the with/end with stuff, I could save some typing. George Raft wrote: Wishing all a happy new year ... I get a runtime error with this piece of code: junk = Application.WorksheetFunction.VLookup("Min", table, Application.WorksheetFunction.Match("Gorge", top, 0)) junk is variant, table and top are ranges. The error is: "Unable to get the Match Property of the WS function" If I replace the ref to App.WSF.Match with an integer, it works fine. Thoughts? Thanks, Tony -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions |