I think you are right. How do we reset the exception? The code has many lines, I will post the relevant part:
.. Usually in similar looping codes of this form that I have this works to overcome this problem.....
…………………………………………………………
Code:
[COLOR=blue]For[/COLOR] irow = 7 [COLOR=blue]To[/COLOR] toc_lastrow
sname = Range("B" & (irow - 1)).Value
[COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] ln320
Worksheet(sname).Select
[COLOR=darkgreen]'--- -- more code ---------[/COLOR]
ln320:
[COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] -1
[COLOR=blue]Next[/COLOR] irow
……..Brief explanation
… As others have indicated.. VBA is ( after your error occurs ) in it’s “Exceptional - state of being” modus “ … - where it “thinks” an error is being handled.
This happened because…. the
On Error you have ….. had the effect of “enabling” your error handler. – (Your error handler is the
On Error Goto ln320, along with the subsequent actions, if any at that ln320 which effect the error state… ),
. This error handler was subsequently activated when the error occurred. It began doing what it should.. In this case going to ln320. VBA now won’t respond to other errors ( in your case the next time an error occurs in the loop ) as VBA ‘aint expecting them. It gets confused if they do come up, “freaks out” and reverts back to it’s “normal” state giving a “normal” default type error…
……
. This line …….…
On Error Goto -1
. …. don’t actually
“go anywhere..” ( It is just a weird syntax ). This is
just a code line (
error statement ) that resets this exceptional state ( In the computer jargon, - “clears the exception”) - ( at the same time as this it deactivates your error handler ( but does not disable it ) and so it can be used again in the loop. Then when the next error occurs, the process starts again…
. That is all a bit much to take in maybe…, see here for a good explanation
On Error WTF? | Excel Matters
http://www.mrexcel.com/forum/excel-questions/847379-error-handler-not-activating.html?
( And here for one in “my language…..” if you feel so inclined…
https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo
.Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.Clear [SOLVED]
…. )
Alan
Bavaria