Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling driving me crazy. Please help
This is driving me mad. Why am I getting this second error? I a combining two workbooks (and doing many other things to them) copyin tables sheet by sheet. Once the sheets in the workbook run out, a error is given. To get around this, I added the On Error GoT ScanTrack step. Then it steps through the second document sheet b sheet until the error: Run-time error '9' Subscript out of range stops the code. I have On Error GoTo ErrRequal. Shouldn't this b enough to step it out of the loop without stopping the code? How do get past this error? Do With Workbooks("Package Tracking3.xls").Worksheets(i) Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row 15)).Copy Cells(65536, 1).End(xlUp)(2).Select ActiveSheet.Paste End With i = i + 1 On Error GoTo ScanTrack Loop ScanTrack: i = 1 Do With Workbooks("Scan Tracking3.xls").Worksheets(i) Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row 15)).Copy Cells(65536, 1).End(xlUp)(2).Select ActiveSheet.Paste End With i = i + 1 On Error GoTo Format Loop Format -- hyyft ----------------------------------------------------------------------- hyyfte's Profile: http://www.excelforum.com/member.php...fo&userid=1318 View this thread: http://www.excelforum.com/showthread.php?threadid=26246 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling driving me crazy. Please help
Hi hyyfte,
I'm not clear on why you're using a do loop when you know how many worksheets are there. Why not something like: For i = 1 to Workbooks("Package Tracking3.xls").Worksheets.Count ''' The rest of your code here Next i For i = 1 to Workbooks("Scan Tracking3.xls").Worksheets.Count ''' The rest of your code here Next i This is a cleaner solution to the task that will plug directly into your existing code and eliminate the use of erros for flow control entirely. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "hyyfte" wrote in message ... This is driving me mad. Why am I getting this second error? I am combining two workbooks (and doing many other things to them) copying tables sheet by sheet. Once the sheets in the workbook run out, an error is given. To get around this, I added the On Error GoTo ScanTrack step. Then it steps through the second document sheet by sheet until the error: Run-time error '9' Subscript out of range, stops the code. I have On Error GoTo ErrRequal. Shouldn't this be enough to step it out of the loop without stopping the code? How do I get past this error? Do With Workbooks("Package Tracking3.xls").Worksheets(i) Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row, 15)).Copy Cells(65536, 1).End(xlUp)(2).Select ActiveSheet.Paste End With i = i + 1 On Error GoTo ScanTrack Loop ScanTrack: i = 1 Do With Workbooks("Scan Tracking3.xls").Worksheets(i) Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row, 15)).Copy Cells(65536, 1).End(xlUp)(2).Select ActiveSheet.Paste End With i = i + 1 On Error GoTo Format Loop Format: -- hyyfte ------------------------------------------------------------------------ hyyfte's Profile: http://www.excelforum.com/member.php...o&userid=13187 View this thread: http://www.excelforum.com/showthread...hreadid=262465 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling driving me crazy. Please help
Every error handler must have a Resume statement unless you are immediately
exiting the sub. The general format is: Sub MyMacro() On Error GoTo ErrorHandler For Counter = 1 to 10 'Do something StartAgain: Next Exit Sub ErrorHandler: 'Fix problem, etc. Resume StartAgain End Sub While you are "in the error handler" you have no error handling. After the Resume error handling will be back, you do not need an other On Error: Sub MyBadMacro() On Error GoTo ErrorHandler For Counter = 1 To 10 x = 1 / 0 StartAgain: Next Exit Sub ErrorHandler: Debug.Print Counter Resume StartAgain End Sub If you want to just ignore the error you can use On Error Resume Next. This is just FYI because that does not appear to be what you want to do. -- Jim Rech Excel MVP "hyyfte" wrote in message ... | | This is driving me mad. Why am I getting this second error? I am | combining two workbooks (and doing many other things to them) copying | tables sheet by sheet. Once the sheets in the workbook run out, an | error is given. To get around this, I added the On Error GoTo | ScanTrack step. Then it steps through the second document sheet by | sheet until the error: Run-time error '9' Subscript out of range, | stops the code. I have On Error GoTo ErrRequal. Shouldn't this be | enough to step it out of the loop without stopping the code? How do I | get past this error? | | | | Do | With Workbooks("Package Tracking3.xls").Worksheets(i) | Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row, | 15)).Copy | Cells(65536, 1).End(xlUp)(2).Select | ActiveSheet.Paste | End With | i = i + 1 | On Error GoTo ScanTrack | Loop | ScanTrack: | i = 1 | Do | With Workbooks("Scan Tracking3.xls").Worksheets(i) | Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row, | 15)).Copy | Cells(65536, 1).End(xlUp)(2).Select | ActiveSheet.Paste | End With | i = i + 1 | On Error GoTo Format | Loop | Format: | | | -- | hyyfte | ------------------------------------------------------------------------ | hyyfte's Profile: http://www.excelforum.com/member.php...o&userid=13187 | View this thread: http://www.excelforum.com/showthread...hreadid=262465 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Driving me CRAZY~ please help | New Users to Excel | |||
Sorry for reposting - but this is driving me crazy! | Excel Discussion (Misc queries) | |||
VLOOKUP driving me crazy | Excel Discussion (Misc queries) | |||
basic...but driving me crazy! | Excel Discussion (Misc queries) | |||
Driving me crazy! | Excel Programming |