MATCH and capturing the error

[Phil_V]

In the help file for MATCH it says:

If MATCH is unsuccessful in finding a match, it returns the #N/A error value.

So I am trying in vain to capture the error, but can't see where I am going wrong.

Please Login or Register  to view this content.

All I get in the watch window for 'response' is 'Empty'
Why aren't I getting the #N/A error?

[DonkeyOte]

In VBA (as was pointed out to me by romperstomper some time back) it's generally easier to use the Application level Match (not the Worksheet Function), I tend to specify the output as a Variant and test IsNumeric status

Please Login or Register  to view this content.

[StephenR]

I think you're overwriting the error with your resume next/goto 0 lines - if you remove them does it work then?

[DonkeyOte]

With handlers it will be empty, without the handler the code will simply debug.

Same is true of say:

Please Login or Register  to view this content.

res is empty

[StephenR]

It is changing from WF to Application that makes it work, even w/o error handling (nothing else in Phil's code needs changing). So what's all that about?

[DonkeyOte]

I don't know the mechanics behind it but the Application level test returns the actual error value (in this case Error 2042), my point is that using this approach you don't need to insert any handlers at all given the variable has been left as Variant (it can hold the Error type), simply validate the variable.

[StephenR]

OK, thanks DonkeyOte.

[Phil_V]

Cheers both, Application.MATCH it is then

I had tried WorksheetFunction.MATCH both with an without the error handling, and as DonkeyOte says, with it it didn't work, without it it just dropped to debug

[Richard Schollar]

To use the Worksheetfunction version you'd just need to test to see if the variant was empty before you turned error reporting back on (or store the result in a variable which you could test after the On Error Goto 0 statement).