Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ON Error goto fails on 2nd error
windows 2000 Pro - Office 2000 Pro
Within an Excel Spreadsheet I have a series of entries that include a company number. I have other sheets in the same workbook that are identified with the company's number. I need to move the entries from the 1st sheet to the corresponding company-specific sheet(s) I go through the rows with the following code ////// Begin Code Segment ///////////////////////////////////////// For i = 3 To last_row retry: 'Return here after adding company sheet Sheets("MAIN").Activate Loc_code = Trim(Sheets("MAIN").Cells(i, "I").Value) 'Company Number On Error GoTo nocompany 'In case company sheet does not exist Range("A" & i & ":I" & i).Copy Sheets(Loc_code).Activate 'This is where debugger highlights error Sheets(Loc_code).Cells(1000, "F").Select Selection.End(xlUp).Select in_row = Selection.Row + 1 Range("A" & in_row).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Next i Exit Sub 'error handlers nocompany: If Err.Number = 9 Then 'Subscript out of range.. Err.Clear On Error GoTo new_error 'In case error occurs trying to add sheet x = ActiveWorkbook.Sheets.Count ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets(x).Select Sheets(x).Copy After:=Sheets(x) Sheets(x + 1).Select Sheets(x + 1).Name = Loc_code Else Debug.Print Err.Number & " " & Err.Description End End If GoTo retry 'Return to "posting sequence for newly added sheet new_error: MsgBox "OOPS What a mess" & Err.Number & " " & Err, desc End Sub /////////////////////End Code Segment/////////////////////////////////////////////// The first time a "new company" shows up and gets added, the routine works fine, adds the sheet and moves on. However, if I have more than 1 "new company" the ON Error does not branch to the error routine (at nocompany:), but halts with a "Subscript Not Found" error. The new worksheet does not get created, but the Loc_code is the appropriate "new company" number.. I've tried recalculating in several areas, and moving the On Error GOTO nocompany statement around, and I've commented out the err.clear statement and the "new_Error code segments..but still have the same problem... SuggestionS?? Thanx in advance... |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On Error Goto ignored | Excel Programming | |||
On Error GoTo | Excel Programming | |||
How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
On error goto 0? | Excel Programming | |||
On error goto 0 | Excel Programming |