Error 91 on Find second time through a loop
 

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?

--
Regards,
Fred

Error 91 on Find second time through a loop
 

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


--
Regards,
Tom Ogilvy


"Fred Smith" wrote in message
...
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?

--
Regards,
Fred

Error 91 on Find second time through a loop
 

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" wrote in message
...
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?

--
Regards,
Fred

Error 91 on Find second time through a loop
 

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.

--
Regards,
Fred


"NickHK" wrote in message
...
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" wrote in message
...
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?

--
Regards,
Fred