Error Handling in a Loop

Dear Gurus,

I recently encountered this issue when writing an error handling section inside a For loop (VBA for Excel). It seems that the On Error status resets to GoTo 0 after the first time an error has been handled.

To illustrate this, in the following situation, I expected 5 Message Boxes but I only get 1. After the error handler activates the next loop, the On Error GoTo NextFor seems to have lost its meaning. My purpose is to have a "On Error GoTo Next Loop" mechanism in place. Could you please help?

Please Login or Register  to view this content.

maybe

Please Login or Register  to view this content.

Without the xit sub line i got 6 messages

Hi Guys,

Thanks for all your prompt inputs. However, I am still stuck as my purpose is to get the script to totally ignore the rest of the current loop as soon as an error has occurred, and jump to the next loop. The code below helps to explain my point.

Please Login or Register  to view this content.

You can handle errors in line:

Please Login or Register  to view this content.

Thanks shg. I think you have solved my problem. With little modification from your code, I got it to skip the current loop if an error happens. Though this is only suitable if you know exactly where the error is likely to occur. For my purpose, this suffices. Thanks again everyone for your valuable contributions.

Please Login or Register  to view this content.

There's no need for a GoTo to do that, but I'm happy if you're happy.

I have a similar Problem. I need to download the latest available zip file with stock prices for a trading day from an online resource. The files are stored online in the format eq[ddmmyy]_csv.zip where the ddmmyy is the date of the trading day that the data pertains to. There will be no files for non-trading days. I therefore created a loop to start with the current date and iterate down one day at a time till a valid file is found. As the URLDownloadToFile did not generate any error whether or not the file was available on line I used a On Error GoTo error handler to return to the loop if an error was generated when the file was unzipped.

The problem is the code works if the available online file is of the current date or one day old. If more than that , on the second iteration I get a 1004 run-time error and the error handler does not seem to capture the error. Any Help would be much appreciated

Please Login or Register  to view this content.