Error Handling behaving weirdly

[firefly2k8]

Any ideas why, rather than returning to the output line, this just goes to the next error handler?

Say for example cells(count,1) produces an error, then it goes to the GeneralErrHandler. But when it gets to the resume statement, it simply goes to the DateErrHandler and then to outputline rather than straight to outputline??

Please Login or Register  to view this content.

[blane245]

A couple of observations.
1) this code does compile as the line "input = " uses the reserved word "input" as a variable name.
2) There are simpler ways to do this type of error handling. For example

Please Login or Register  to view this content.

Date errors would be handled similarly. Check input the Err object for what other kind of information it can provide you about an error.

[firefly2k8]

In the actual code i don't use the parameter input - but good spot.

I like your idea here. But the error typicaly occurs when the following function fails to find a lookup value:

On Error Resume Next
SearchCol = Application.Match(Pair, Range(BBDataSearchRow & ":" & BBDataSearchRow), 0)

Using a watch, it seems err.number remains zero even when "on error resume next" prevents execution failure.

Is there any way I can detect when SearchCol returns an error without using On Error Goto... ?

[blane245]

Not familar with Application.Match. It's not in Excel 2003, but you can use IsEmpty or worksheet functions like IsNa to check for values returned that are not considered VBA errors.

[firefly2k8]

I hope it is excel 2003, since I am using excel 2003!!

i'll try your suggestion.

Thank you

[blane245]

Oh, I normally use the Application.WorksheetFunction qualifier. I didn't know you were referring to the Excel Match function. Hope my suggestion helps.

[romperstomper]

Application.Match does not cause a run-time error (unlike WorksheetFunction.Match) which is why the err.number is 0. You need to store the result in a Variant and check it using Iserror:

Please Login or Register  to view this content.

[firefly2k8]

I was advised the application.worksheetfunction.match was unreliable and i should use application.match instead.

however, sometimes when application.match fails to find a value it returns Error 2402 - in which case iserror picks it up nicely - happy days.

but sometimes when application.match fails to find a value it returns 0!

i can do: if iserror(SearchCol) or SearchCol = 0

but it is starting to get clumsy!

[romperstomper]

I was advised the application.worksheetfunction.match was unreliable and i should use application.match instead.

By whom?

but sometimes when application.match fails to find a value it returns 0!

I have never seen that.
The only way I can see that happening would be if you declared SearchCol as an integer or long and put an on error resume next statement in front of it. Then SearchCol would be 0, but that would not be what Application.Match actually returned - it always returns Error 2042 if the value is not found.

[firefly2k8]

you are quite correct romperstomper, I had SearchCol defined as long. With it defined as a variant all is working.

I was advised about application.match on this forum when i encountered a different problem. can't recall who/what/where/why.

Thanks for your help all.

[romperstomper]

For the record, the worksheetfunction version is not unreliable - the only difference is that it raises a run-time error if the value is not found.

Please don't forget to mark the thread 'Solved'.

[blane245]

For the record, the worksheetfunction version is not unreliable - the only difference is that it raises a run-time error if the value is not found.

Please don't forget to mark the thread 'Solved'.

So would another way to approach this would be to use the worksheetfunction version and an On Error? Does this approach work from all worksheetfunctions?

[romperstomper]

Yes, and yes. (AFAIK, anything you can do via the WorksheetFunction object you can do via Application instead)