On Error GoTo stops working

[Noemi]

Hi

I have on On Error Goto inside a loop and the first time it has an error it
goes to the section which performs are task but the second time it has an
error it crashs and I get the error 9 message.

Here is my code

Dim stName As String
Dim stThe As String
Dim stLetter As String
Dim dbLong As Double

Sheets("Temp").Select
Range("A2").Select

Do

stName = ActiveCell.Value
stLetter = Left(stName, 1)
If stLetter = "Y" Or stLetter = "Z" Then
stLetter = "X-Z"
End If
On Error GoTo BoldLine
Sheets(stLetter).Select
(more code)
BoldLine:
Sheets("Temp").Select
ActiveCell.Font.Bold = True
(more code)
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""

Thanks Noemi

 

[Leith Ross]

Hello Noemi,

It looks like the error occurs because you are trying to reference
worksheet that doesn't exist (Subscript out of range). The *On Erro
GoTo* statement is intended to trap errors, identify them, clear them
and continue or stop code execution. What are you trying to achieve?

Sincerely,
Leith Ros

 

[Noemi]

Hi Leith

I have a workbook which contains a worksheet with each letter of the
alphabet and it is used to store company names.

When I have a new list of names I use the code to check the new names
against each current name within each worksheet and the way this is done is I
take the first letter of the name and select the sheet based on that

however

on occassion I get names starting with numbers or symbols and when it is a
number then it checks under sheet A however when it is a symbol then I will
be deleting the name as it is incorrect. As I was not able to look to see if
the first letter was a symbol I have placed a On Error GoTo cause it error's
whenever it tries to find a sheet which doesn't exist.

The first time around it works but when it comes across another symbol then
it gets the runtime error instead of going to the location which prepares the
name for deletion like previous.

I hope this makes sense

Thanks Noemi

 

[Rick Rothstein]

You are not really using the Error object correctly... the On Error GoTo's
code (where the statement sends execution when an error occurs) should not
be inside a loop. To properly run an On Error GoTo trap, you need a Resume
statement (there are 3 variations for this statement) inside the code the
error sends execution to in order for it to take you out of the error code
and back into your main code... you cannot do that from inside a loop (well,
you might be able to doctor up a kludge to do it, but it would be horrible
programming practice to do so). You might be able to use and On Error Resume
Next and do testing for Err.Number to see if an error occurred and direct
your code that way, but it is hard to give you advice because of your "(more
code)" sections. Try looking up the "On Error Statement" and the "Resume
Statement" in the help files to get an idea of how that combination is meant
to work.

 

[Leith Ross]

Hello Noemi,

This macro should get you started. It checks the first character of
name in column "A" of the worksheet "Temp" starting at row 2 the last
entry in the column. If the worksheet name exists then the name is made
bold.

Code:
--------------------

Dim Cell As Range
Dim Alpha As Boolean
Dim Number As Boolean
Dim Rng As Range
Dim stLetter As String
Dim stName As String

With Worksheets("Temp")
Set Rng = .Range("A2", .Cells(2, "A").End(xlUp))
End With

For Each Cell In Rng
stName = Cell.Text
'Test the first character of the name
Alpha = IIf(stName Like "[A-ZAa-z]*", True, False)
Number = IIf(stName Like "#*", True, False)
If Alpha Then
'Check if the first letter is X,Y,Z
If stName Like "[X-Zx-z]*" Then
stLetter = "X-Z"
Else
stLetter = Left(stName, 1)
End If
End If
If Number Then stName = "A"
On Error Resume Next
stName = Worksheets(stName).Name
If Err = 9 Then
Err.Clear
Else
If Alpha Or Number Then Cell.Font.Bold = True
End If
On Error GoTo 0
Next Cell