Error Handling in Excel Macro

[Ned the music guy]

I have been trying to figure out how to make an Excel 2010 macro not give me an error when "find" does not find what it is looking for.

I added Error Handler at the front of the macro and Error Handler "resume next" after the "find", but I need it to skip all the finds and go to a specific line, not just resume. Thanks very much for any help.

[TMS]

You need On error Resume Next BEFORE the action that may error and On Error Goto 0 afterwards.

Regards, TMS

[Ned the music guy]

TMS, Your answer worked perfectly. I could have saved hours of searching and working to find the answer had I just posted this before. Thank You, Thank You, Thank You.

[TMS]

You're welcome. Thanks for the rep. You learn by trying and failing so the time was not all wasted

[Ned the music guy]

Thanks again you must be an optimistic person. I guess my time wasn't wasted after all. Is there a way to have the macro upon error, go to a specific line further down? Or maybe even run a different macro? The resume next works great, but I also need to make the macro skip further instructions upon error. Thanks again.

[TMS]

Sure, you'd use something like:

Please Login or Register  to view this content.

This is just a simplistic approach. You'd need to be more specific about what you are trying to do, what errors you might need to process, and what you want to do when you get one in order for anyone to offer a practical solution.

Best bet is to look for examples of error handling routines. I rarely (close to never) use On "Error Goto Label"

Regards, TMS

[Ned the music guy]

Many thanks. Could I send you a simple excel spreadsheet with proposed macro so you can see what I am talking about?

[TMS]

You're better uploading it to the forum so more people can offer help.

Regards, TMS

[Ned the music guy]

OK great. I'll do that.

Thanks again

[Ned the music guy]

On your code:

On Error Goto ErrorHandler
' your code (what code do I use here to stop the macro and go to the ErrorHandler?)
'

Exit Sub

ErrorHandler:

I put for my code:
Application.Run "Book2!Macro7"

I put this before and after the line that will eventually cause the error (a "find" search). The ErrorHandler goes to Macro 7 whether there is an error or not.

Much appreciation!

[Mordred]

As TMShucks stated you should upload a workbook. With code tags

Please Login or Register  to view this content.

is where the error is caught,

Please Login or Register  to view this content.

is where you put the code where you want the error to be handled, example

Please Login or Register  to view this content.

If you want something more specific, upload a sanitized workbook with mock data with a before and after scenario too. This will help us help you tremendously.

Cheers

[Ned the music guy]

Thanks again, here is the file. The scenario is on the workbook.

[TMS]

Actually, for this specific example, you don't need error handling:

Please Login or Register  to view this content.

Regards, TMS

[Ned the music guy]

Your test works exactly right. Your help is appreciated more than I can tell you. Obviously, I am not an accomplished Excel programmer, but I can usually get things done with my limited knowledge. My way almost always causes the macro to be cumbersome and slow because of my limits, but it usually works.

I tend to obsess about things I cannot figure out. For future reference and so I can sleep at night, would it be possible to let me know the code for the errorhandler method that I was trying? I won't use it now, but it might come in handy in the future. Again, thanks so much for your time.

[TMS]

would it be possible to let me know the code for the errorhandler method that I was trying?

To be honest, no. Not because I'm trying to be difficult, just because I don't use it so I can't really quote an example which I feel confident would work. I have already outlined the method and Mordred has clarified the mechanics.

Chip Pearson has a very detailed explanation of Error Handling here: Error Handling

As I said, I tend to adopt the "On Error Resume Next" approach, test the error condition and, if necessary, take action in the next lines, and switch off the error handling with "On Error Goto 0". This means the error trapping is very localised, I'm in control, and it's switched off again as soon as possible. That, in turn, means that, if I get an error that I wasn't expecting, the code will crash ... but then the problem is highlighted and can be fixed ... albeit with some more error handling.

Regards, TMS

[Ned the music guy]

Understood. Thanks again for your help.

[JosephP]

btw, you also don't need error handling if you use Find. rather than

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

[Ned the music guy]

Thanks, all the help is appreciated.