Error Handling strange behaviour, it only works the first time

[Henk Stander]

Good day,

I have used Error handling to prevent the macro from crashing and it works fine, except if you branch back to the body of the macro and hit the same error provoking condition again.!?!?

The code below is used to search through a data base of chemicals and to extract the row of data for the chemical if found. The error handling is to prevent the macro from crashing if the chemical searched for is not present in the list.

If the chemical searched for (search string) is not found, the operator is asked for a new search string, if the new search string is present the macro works fine, HOWEVER, if the new search string is also not present, the ERROR HANDLER is IGNORED!!!

I would appreciate any help to sort out this, for me, STRANGE BEHAVIOUR.

Please Login or Register  to view this content.

[JapanDave]

Try this,

Please Login or Register  to view this content.

[JosephP]

you can't use goto to exit an error handler-you have to use a resume statement

anyway you don't really need an error handler there you can test directly if the string is found before trying to use the result

Please Login or Register  to view this content.

[JosephP]

also

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[haripopuri]

Hi Henk,

Error Handler handles error one time only. You can try using Err.Number for multiple error handling.
I have changed named ranges in your code for my easy sake. Below code should work with a bit tweaking from your end. Let me know if it works.

Please Login or Register  to view this content.

[JapanDave]

Hi Henk,

Error Handler handles error one time only. You can try using Err.Number for multiple error handling.

That is false. If that were true "On Error Resume Next" would only work once in a loop situation.

[JosephP]

technically it's true. an error handler only handles the error that brought the processing to it. until you hit a resume statement (or leave the routine) any further error will be unhandled-which is the problem here. on error resume next effectively resets itself every time it is triggered so it only handles one error each pass

[Henk Stander]

Thank you guys, the problem has been solved with replacing "goto" with "Resume"

http://www.vbaexpress.com/forum/newr...ote=1&p=280309

[Henk Stander]

Hi JapanDave,

This didn't work, see explanation from JosephP, the goto is the problem.

Thanks

[Henk Stander]

also

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Noted, thanks.