Error Handling problem

Not sure why excel is doing this... I have this code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/23/2005 by andya
Dim c As OLEObject
For x = 1 To Worksheets.Count
On Error GoTo ErrHand
For Each c In Worksheets(x)
'do stuff
Next c
ErrHand:
Next x

End Sub

This is what happens. When I step through the code the error handling works
fine when x=1. But on the next trip through (x=2), It gives me Run-time
error '438' on the "For Each c in Worksheets(x)" line and doesn't use the
error handling. Why?? Thanks for your help.

Look in excel VBA help at the resume statement. Until you issue a resume,
you are still in error handling mode and the next error causes excel to
quit.

--
Regards,
Tom Ogilvy


"Brassman" <[email protected]> wrote in message
news:[email protected]...
> Not sure why excel is doing this... I have this code:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 5/23/2005 by andya
> Dim c As OLEObject
> For x = 1 To Worksheets.Count
> On Error GoTo ErrHand
> For Each c In Worksheets(x)
> 'do stuff
> Next c
> ErrHand:
> Next x
>
> End Sub
>
> This is what happens. When I step through the code the error handling
works
> fine when x=1. But on the next trip through (x=2), It gives me Run-time
> error '438' on the "For Each c in Worksheets(x)" line and doesn't use the
> error handling. Why?? Thanks for your help.
>
>

"Brassman" <[email protected]> wrote in message
news:[email protected]...
> Not sure why excel is doing this... I have this code:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 5/23/2005 by andya
> Dim c As OLEObject
> For x = 1 To Worksheets.Count
> On Error GoTo ErrHand
> For Each c In Worksheets(x)
> 'do stuff
> Next c
> ErrHand:
> Next x
>
> End Sub
>
> This is what happens. When I step through the code the error handling
> works
> fine when x=1. But on the next trip through (x=2), It gives me Run-time
> error '438' on the "For Each c in Worksheets(x)" line and doesn't use the
> error handling. Why?? Thanks for your help.
>
Put your error trapper outside the For ...Next

Excel doesn't handle Worksheets looping very well. In most cases, you need to activate each Worksheet with activate or select command before you do your stuff.
The following amended code should work without the use of error handling.

Sub Looper()
Dim c As OLEObject
For x = 1 To Worksheets.Count
Worksheets(x).activate
For Each c In Worksheets(x)
'do stuff
Next c
ErrHand:
Next x

End Sub


hth
david

Thanks. I had an error in my code too. It should have been:

For Each c In Worksheets(x).OLEObjects
instead of
For Each c In Worksheets(x)


"Tom Ogilvy" wrote:

> Look in excel VBA help at the resume statement. Until you issue a resume,
> you are still in error handling mode and the next error causes excel to
> quit.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Brassman" <[email protected]> wrote in message
> news:[email protected]...
> > Not sure why excel is doing this... I have this code:
> >
> > Sub Macro1()
> > '
> > ' Macro1 Macro
> > ' Macro recorded 5/23/2005 by andya
> > Dim c As OLEObject
> > For x = 1 To Worksheets.Count
> > On Error GoTo ErrHand
> > For Each c In Worksheets(x)
> > 'do stuff
> > Next c
> > ErrHand:
> > Next x
> >
> > End Sub
> >
> > This is what happens. When I step through the code the error handling
> works
> > fine when x=1. But on the next trip through (x=2), It gives me Run-time
> > error '438' on the "For Each c in Worksheets(x)" line and doesn't use the
> > error handling. Why?? Thanks for your help.
> >
> >
>
>
>

Sorry, try this final amended version. (relevant changes bolded)

Sub Looper()
Dim c As Range
For x = 1 To Worksheets.Count
Worksheets(x).Activate
For Each c In ActiveSheet.UsedRange
'begin testing lines
If Not IsEmpty(c) Then
c.Interior.Color = vbBlue
End If
'end of testing lines
Next
Next
End Sub