Error Handling then continue running

[modo8]

I've having some trouble understanding how to do this Error Handling thing.
Any assistance that's offered is greatly appreciated! My coding is at the
end.

The macro is opening a series of files, manipulating them, then resaving
them with a new name. The file names and manipulations are all listed in a
spreadsheet; the macro pulls one row of information, does its thing, then
moves on to the next row until it reaches its stop command.

Where I'm having trouble with the error handling is getting the macro to
continue on its way after an error (if I understand correctly what's going
on, it actually is continuing on its way, but then stops upon reaching a
second row with an error)

My goal is to have any errors put a message in a cell of the offending row,
rather than a pop-up message. There's actually a couple loops in the macro,
my thought is that I can put in several "on error goto" err_handler1,
err_handler2, etc., and have each err_handler populate a different message.
Depending on the step where the error occurs, I can then populate that cell
with a message that instructs the user what correction is necessary.

So here's the coding I was using on the first loop. Thanks in advance for
any help!

Do Until Grow = NextRow

Windows("Personal.xls").Activate
Sheets("Info Delivery").Activate

If IsEmpty(Cells(Grow, 28)) = True Then GFolder = Cells(Grow, 27)
Else GFolder = Cells(Grow, 27) & "\" & Cells(Grow, 28)
ChDir SourcePath & GFolder

If Cells(Grow, 41) = "Y" Then
Application.EnableEvents = False

On Error GoTo Err_Hdler1
Workbooks.Open Filename:=SourcePath & GFolder & "\" &
Cells(Grow, 33)
Application.EnableEvents = True

End If

Err_Hdler1:
Select Case Err.Number
Case Is = 1004
Range("F" & SummaryRow) = "File Not Found"
Case Else
Range("F" & SummaryRow) = "Unknown Error"
End Select

SummaryRow = SummaryRow + 1
Grow = Grow + 1
Loop

 

[Jim Cone]

Put error handling outside of the rest of the code.
To user error handling to write to the worksheet, structure it like this...
'(Note the "Exit Sub" and the "Resume Next")
'--
Sub GoAround()
On Error GoTo Err_Hdler1 '<<< At the top
'Declare variables here.
Sheets("Info Delivery").Activate
'Windows("Personal.xls").Activate '<<< What is this?

Do Until Grow = NextRow
If IsEmpty(Cells(Grow, 28)) = True Then
GFolder = Cells(Grow, 27)
Else
GFolder = Cells(Grow, 27) & "\" & Cells(Grow, 28)
End If
ChDir SourcePath & GFolder

If Cells(Grow, 41) = "Y" Then
Application.EnableEvents = False
Workbooks.Open Filename:=SourcePath & GFolder & "\" & Cells(Grow, 33)
Application.EnableEvents = True
End If
Loop
Exit Sub

Err_Hdler1:
Select Case Err.Number
Case Is = 1004
Range("F" & SummaryRow) = "File Not Found"
Case Else
Range("F" & SummaryRow) = "Unknown Error"
End Select
SummaryRow = SummaryRow + 1
Grow = Grow + 1
Resume Next
End Sub
--
Jim Cone
Portland, Oregon USA




"modo8" <[email protected]>
wrote in message
I've having some trouble understanding how to do this Error Handling thing.
Any assistance that's offered is greatly appreciated!
My coding is at the end.

The macro is opening a series of files, manipulating them, then resaving
them with a new name. The file names and manipulations are all listed in a
spreadsheet; the macro pulls one row of information, does its thing, then
moves on to the next row until it reaches its stop command.

Where I'm having trouble with the error handling is getting the macro to
continue on its way after an error (if I understand correctly what's going
on, it actually is continuing on its way, but then stops upon reaching a
second row with an error)

My goal is to have any errors put a message in a cell of the offending row,
rather than a pop-up message. There's actually a couple loops in the macro,
my thought is that I can put in several "on error goto" err_handler1,
err_handler2, etc., and have each err_handler populate a different message.
Depending on the step where the error occurs, I can then populate that cell
with a message that instructs the user what correction is necessary.

So here's the coding I was using on the first loop. Thanks in advance for
any help!

Do Until Grow = NextRow
Windows("Personal.xls").Activate
Sheets("Info Delivery").Activate
If IsEmpty(Cells(Grow, 28)) = True Then GFolder = Cells(Grow, 27)
Else GFolder = Cells(Grow, 27) & "\" & Cells(Grow, 28)
ChDir SourcePath & GFolder
If Cells(Grow, 41) = "Y" Then
Application.EnableEvents = False
On Error GoTo Err_Hdler1
Workbooks.Open Filename:=SourcePath & GFolder & "\" &
Cells(Grow, 33)
Application.EnableEvents = True
End If
Err_Hdler1:
Select Case Err.Number
Case Is = 1004
Range("F" & SummaryRow) = "File Not Found"
Case Else
Range("F" & SummaryRow) = "Unknown Error"
End Select
SummaryRow = SummaryRow + 1
Grow = Grow + 1
Loop

 

[Maury Markowitz]

Error handling in VBA is far from optimal. The best way to deal with a
"looping attempt" is something along the lines of...

On Error Resume Next
Workbooks.Open....
if err.number <> 0 then
report it
err.clear
end if

If you put an On Error Goto inside a loop, it will only "work" the
first time, and from then on the errors will not get trapped. I'm sure
there's a way around that, but the solution above should do what you
want.

 

[modo8]

Thank you both so much! I went with Maury's way and it worked well. I was
able to use the same language in each loop to handle different errors.

For anyone else with this problem, here's the coding I wound up using:

Do Until Grow = NextRow

On Error Resume Next

Windows("Personal.xls").Activate
Sheets("Info Delivery").Activate

If IsEmpty(Cells(Grow, 28)) = True Then GFolder = Cells(Grow, 27) Else
GFolder = Cells(Grow, 27) & "\" & Cells(Grow, 28)
ChDir SourcePath & GFolder

If Cells(Grow, 41) = "Y" Then
Application.EnableEvents = False
Workbooks.Open Filename:=SourcePath & GFolder & "\" & Cells(Grow, 33)

If Err.Number <> 0 Then
If Err.Number = 1004 Then Range("F" & SummaryRow) = "File not
found" else Range("F" & SummaryRow) = "Unknown error"
Err.Clear
End If

Application.EnableEvents = True

End If

SummaryRow = SummaryRow + 1
Grow = Grow + 1
Loop