On Errror Goto Error handler only works first time through "For Each" cycle

[lisamariechemistry]

Where's my bug? I have a nice module for selecting a range of cells
in workbookA, opening a workbookB based on the cell contents, and
transferring some data back to workbook A. If one of the selected
cells is not an appropriate entry, I have an error handler set up to
give a little message and move on to the next cell. The problem is
that the error handler only works once! If a given cell triggers the
error handler, it routes properly to my message box and then on to
process the next cell in the selection. But if a second cell is
"improper", the error handler is not triggered, and the resulting
error message terminates the module. I have already tried getting rid
of the "On Error Goto 0" to end error trapping, that didn't make any
difference. (Not that I thought it should, because error trapping
should be reinitiated the next cycle through the "For Each" statement,
right?)

here's some (simplified) code:

Set MyRange = Selection

For each cell in MyRange.Cells
MyRow = cell.row
MySampleName = Cells(MyRow, 2).Value
MyPath = "\\c\data\" & MySampleName & ".xls"

On Error GoTo Errorhandler
Workbooks.Open Filename:=MyPath
On Error GoTo 0

'...here is a bunch of data manipulation, works fine, not interesting

NextCell:
Next Cell
Exit Sub
Errorhandler:
MsbBox "Not a valid Sample Name."
GoTo NextCell
End Sub

 

[Jim Thomlinson]

When you encounter an error your system goes to the error handler. The code
will remain in error handler mode until it reaches either a resume, resume
next, exit or end command to clear the error buffer (you can also just clear
the error with Err.Clear). You never reach one of those so your code remains
in error mode and the error is held in the error buffer. The next time you
hit an error you crash...

Perhaps try something more like this...
Dim wbkOpen as workbook

Set MyRange = Selection

For each cell in MyRange.Cells
MyRow = cell.row
MySampleName = Cells(MyRow, 2).Value
MyPath = "\\c\data\" & MySampleName & ".xls"

On Error resume next
set wbkOpen = Workbooks.Open Filename:=MyPath
On Error GoTo 0

if wbkopen is nothing then
MsbBox "Not a valid Sample Name."
else
'...here is a bunch of data manipulation, works fine, not interesting
end if
next Cell
End Sub

 

[lisamariechemistry]

When you encounter an error your system goes to the error handler. The code
will remain in error handler mode until it reaches either a resume, resume
next, exit or end command to clear the error buffer (you can also just clear
the error with Err.Clear). You never reach one of those so your code remains
in error mode and the error is held in the error buffer. The next time you
hit an error you crash...

Perhaps try something more like this...
Dim wbkOpen as workbook

Set MyRange = Selection

For each cell in MyRange.Cells
MyRow = cell.row
MySampleName = Cells(MyRow, 2).Value
MyPath = "\\c\data\" & MySampleName & ".xls"

On Error resume next
set wbkOpen = Workbooks.Open Filename:=MyPath
On Error GoTo 0

if wbkopen is nothing then
MsbBox "Not a valid Sample Name."
else
'...here is a bunch of data manipulation, works fine, not interesting
end if
next Cell
End Sub

--
HTH...

Jim Thomlinson











- Show quoted text -

Thank you and thanks for the explanation...I'm learning a lot of
visual basic by trial and error, so the "why" of it is very helpful.