BAC,

The 2 code examples show the correct way of handling multiple errors. Code
originally form Jim Rech, Excel MVP, IIRC.

HTH,
Bernie
MS Excel MVP

Sub ProperWayToUseMulitpleErrorHandlers()

Dim boolTest1 As Boolean
Dim boolTest2 As Boolean
Dim boolTest3 As Boolean
Dim Tab1Start As Long
Dim Tab2Start As Long

Check1:
On Error GoTo ErrCheck1
Columns("B").Find(What:="Length").Activate
boolTest1 = True

Check2:
On Error GoTo ErrCheck2
Tab1Start = Columns("B").Find(What:="MD").Row
boolTest2 = True

Check3:
On Error GoTo ErrCheck3
Tab2Start = Columns("B").Find(What:="East").Row
boolTest3 = True

Stopp:
If Not boolTest3 Then
MsgBox "Unknown format"
End If

Exit Sub

ErrCheck1: Resume Check2
ErrCheck2: Resume Check3
ErrCheck3: Resume Stopp

End Sub

Sub HandleMultipleErrors()
Dim i As Integer

On Error GoTo myErr:

For i = 1 To 20
MsgBox i / (i Mod 2)
noErr:
Next i

Exit Sub

myErr: Resume noErr

End Sub


"BAC" wrote in message
...
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...