On Error GoTo stops working

N

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
 
N

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
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top