Problem w/ Match prop vs. Match method

[George Raft]

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]

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]

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]

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]

Thanks again Dave, that helps.

Tony

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.