Error 91 on Find second time through a loop

[Fred Smith]

I have the following code in a For loop which goes through every file in a
folder:

On Error Goto NoData
Columns("A:A").Select
Cells.Find(What:="06/30/2003").Activate

I'm having trouble getting the On Error to work. The first file where the find
fails works fine -- it goes to the NoData statement and continues on. With the
second file, I get an error 91: Object variable or With block variable not set.

How do I get the On Error statement to take effect with every file, not just the
first?

 

[Tom Ogilvy]

If you don't have a resume statement in your error handler, then you never
end being in the error handler - then when the second error occurs, VBA
gives up on you - an error in a error handler??? - so quit. Look at the vba
help on Resume to see what you want to do.

I don't know what your error handler is doing, but you might want to just do

Columns("A:A").Select
On Error resume Next
Cells.Find(What:="06/30/2003").Activate
On Error goto 0


better would be

Dim rng as Range

set rng = Cells.Find(What:="06/30/2003")
if not rng is nothing then
rng.Activate
end if

 

[NickHK]

Fred,
Hard to answer, as I have no idea what yor 'NoData" error handler does.
However, you can avoid the whole question and make you code better into the
bargain.

Dim FoundCell as Range
On Error Resume Next
Set FoundCell = Range("A:A").Find(What:="06/30/2003")
On Error Goto 0
If FoundCell is nothing then
'No data
else
msgbox FoundCell.Address
end if

NickHK

 

[Fred Smith]

Thanks for your help, Tom and Nick.

As an old Fortran programmer, I thought the GoTo part of On Error just allowed
me to skip around code I didn't want executed. I didn't know it was supposed to
be an error handling routine.

I'll definitely use your proposed code.