strange behavior errorhandling

[Jack Sons]

Hi all,

I have a directory with some 500 files with names 8.xls 23.xls
163.xls 166.xls and so on to 1842.xls.
In each of those files I want to change all dates with 2004 into the
corresponding 2003 dates.
In my code (see below) I call and open a file, say 163.xls, search and
replace 2004 into 2003 and then save the file and close it.
Then I call and open the file with a file number that is one higher than the
previous one, so in my example 164.xls.
Because many file numbers do not exist, like 164.xls in my example, I have
in my code a line that reads:

On Error GoTo ErIsGeenBestand

Now the thing that I don't understand. This all works well, but only once.
In the case of trying to open the non-existing 164.xls file, the execution
of the code indeed jumps from

Workbooks.Open Filename:=k & ".xls"
to
ErIsGeenBestand:

but when after that in the next loop the next non-existing file (165.xls) is
called the code will not jump again. I get an errormassage saying that
165.xls can't be found and please check the spelling of its name.

Why will On Error GoTo not work twice in a row? What is wrong with my code?

Jack Sons
The Netherlands
----------------------------------------------------------------------------
-------------------------------------------------
Sub MacroVan2004Naar2003()

Application.ScreenUpdating = False

ChDir "C:\Documents and Settings\2\Mijn documenten\Excel\OverzichtUlt2003"
Application.ScreenUpdating = False

k = 163

Do Until k = 189

On Error GoTo ErIsGeenBestand

Workbooks.Open Filename:=k & ".xls"

Columns("A:A").Select
Selection.Replace What:="2004", Replacement:="2003", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select

ActiveWorkbook.Save

ActiveWorkbook.Close

ErIsGeenBestand:

k = k + 1
Loop

Application.ScreenUpdating = True

End Sub

 

[Jim Cone]

Jack Sons,

Hello again,

The error is not being reset after it occurs.
Add "Err.Clear" immediately after ErIsGeenBestand:
at the bottom of your code.

My recommendation should work, (I can't test it), however, it is not
the proper way to handle errors in code.

Regards,
Jim Cone
San Francisco, CA

 

[jeff]

I think you have to reset your error handling.

Assume you have non-zero numbers in A1:A10,
except for A5 which is zero. Select A1:A10.

The following code loops thru your selection
and when it hits the division by zero, it
handles the error then resets.
It's not an elegant example, but works.

Other, better ways out there????

Sub d()
Dim r As Range
Set r = Selection
For Each c In r
On Error GoTo H:
x = 5 / c
GoTo cont:
H:
Resume Next
cont:
Next c
End Sub

 

[Frank Kabel]

Hi Jack
try the following:

Sub MacroVan2004Naar2003()
Application.ScreenUpdating = False
ChDir "C:\Documents and Settings\2\Mijn
documenten\Excel\OverzichtUlt2003"
Application.ScreenUpdating = False
k = 163

Do Until k = 189
On Error resume next
Workbooks.Open Filename:=k & ".xls"
if err.number<>0 then
msgbox "Following file not found: " & k & ".xls"
else
Columns("A:A").Select
Selection.Replace What:="2004", Replacement:="2003",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
endif
on error goto 0
k = k + 1
Loop

Application.ScreenUpdating = True

End Sub

 

[Jack Sons]

Frank,

More than a thousand files are "missing" because of the 1842 numbers 1 to
1842 only approximately 500 numbers are used.
Answering1300 times a messagebox is very tedious.
I want the loop to go on while it regards numbers of which there are no
files, in effect skipping the main part (the open-find-change-save-close
part) of the procedure if a file does not exist. In case a file do exist the
sub should do its work (changing 2004 in 2003).

If the sub calls a non-existing file an error will occur. The standard way
to handle that is - I thought - a line of code with On Error GoTo LineX
before the point where the error can occur, and a line of code with "LineX:"
where it should go on after skipping the proper part of the code.
Apparently this line of thought somewhere is wrong but I don't know where
and why.
Even your kind answer and that of Jim and Jeff made it not clear to me.
So please enlighten me, I need to solve this.

Jack.

 

[Frank Kabel]

Hi
have you tried the code?. You may comment the messagebox in your case
though but it should do

 

[Jack Sons]

Frank,

It worked as you said, although I still do not understand what was wrong
with my code. Nevertheless a thousand thanks.

Jack.

 

[Dave Peterson]

Jim Cone's reply that you didn't reset the error was the reason your code
failed.

Pop in some msgboxes into you loop's code:


Do Until k = 189

MsgBox Err.Number
On Error GoTo ErIsGeenBestand

Workbooks.Open Filename:=k & ".xls"

Columns("A:A").Select
Selection.Replace What:="2004", Replacement:="2003", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select

ActiveWorkbook.Save

ActiveWorkbook.Close

ErIsGeenBestand:

k = k + 1
MsgBox Err.Number
Loop

You'll see that once you hit the error, it stays active.

And from xl2002 VBA's help for "on error"


....an "active" error handler is an enabled handler that is in the process of
handling an error. If an error occurs while an error handler is active (between
the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit
Property statement), the current procedure's error handler can't handle the
error.

So you had to reset your error handler--like Frank did.

 

[Frank Kabel]

Hi Jack
Probelm with your was that you didn't reset the error handling after
the first error occured