Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Errror Goto Error handler only works first time through "For Each" cycle
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Errror Goto Error handler only works first time through "For Ea
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 " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Errror Goto Error handler only works first time through "For Ea
On May 11, 1:23 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: 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 " wrote: 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- Hide quoted text - - 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on "On Error GoTo skip" | Excel Discussion (Misc queries) | |||
"On Error GoTo" syntax problem | Excel Programming | |||
Can I place "On Error GOTO xxx" into module level? | Excel Programming | |||
Subroutine vs GOTO "On Error" | Excel Programming |