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...

This is not strictly a proper use of the error handler. The error handler is
more there to catch errors which you would not expect to occure in the normal
course of execution. For example if a network connection goes down. To fix
your problem why not create a function that tells you if the sheet exists or
not. Something similar to (untested):

private function SheetExists(byval strSheetName as string) as boolean
dim wks as worksheet

set wks = sheets(strsheetname)

if wks is nothing then
SheetExists = false
else
SheetExists = True
endif
end function

Now that you know if the sheet exists you cna handle that withing the normal
exectution of your procedure with an if statement

if not sheetexist(Loc_code) then
'create the sheet with existing error code
endif

HTH

"BAC" wrote:

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...

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...

The Function you offered generated the "Subscript out of range " on the set
wks line

So I modified it to:


Function SheetExists(ByVal strSheetName As String) As Boolean
Dim wks As Worksheet

On Error GoTo nosheet
Set wks = Sheets(strSheetName)
SheetExists = True
Exit Function
nosheet:
SheetExists = False

End Function



which appears to be working fine...

Thanx for the help!!




"Jim Thomlinson" wrote:

This is not strictly a proper use of the error handler. The error handler is
more there to catch errors which you would not expect to occure in the normal
course of execution. For example if a network connection goes down. To fix
your problem why not create a function that tells you if the sheet exists or
not. Something similar to (untested):

private function SheetExists(byval strSheetName as string) as boolean
dim wks as worksheet

set wks = sheets(strsheetname)

if wks is nothing then
SheetExists = false
else
SheetExists = True
endif
end function

Now that you know if the sheet exists you cna handle that withing the normal
exectution of your procedure with an if statement

if not sheetexist(Loc_code) then
'create the sheet with existing error code
endif

HTH

"BAC" wrote:

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...

Sorry I thought that would work . Here is a function that does not rely on
the error handler

Private Function SheetExists(ByVal strSheetName As String) As Boolean
Dim wks As Worksheet
Dim blnReturnValue As Boolean

blnReturnValue = False
For Each wks In Worksheets
If wks.Name = strSheetName Then
SheetExists = True
Exit Function
End If
Next wks
SheetExists = blnReturnValue
End Function

Sorry...

"BAC" wrote:


The Function you offered generated the "Subscript out of range " on the set
wks line

So I modified it to:


Function SheetExists(ByVal strSheetName As String) As Boolean
Dim wks As Worksheet

On Error GoTo nosheet
Set wks = Sheets(strSheetName)
SheetExists = True
Exit Function
nosheet:
SheetExists = False

End Function



which appears to be working fine...

Thanx for the help!!




"Jim Thomlinson" wrote:

This is not strictly a proper use of the error handler. The error handler is
more there to catch errors which you would not expect to occure in the normal
course of execution. For example if a network connection goes down. To fix
your problem why not create a function that tells you if the sheet exists or
not. Something similar to (untested):

private function SheetExists(byval strSheetName as string) as boolean
dim wks as worksheet

set wks = sheets(strsheetname)

if wks is nothing then
SheetExists = false
else
SheetExists = True
endif
end function

Now that you know if the sheet exists you cna handle that withing the normal
exectution of your procedure with an if statement

if not sheetexist(Loc_code) then
'create the sheet with existing error code
endif

HTH

"BAC" wrote:

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...