FindNext Errors

[Geoff]

I simply cannot get the Help example on FindNext to work. Even explicitly
setting the find paramaeters still results in err 91. The example cell value
is changed but then errors out at Loop While.

The problem seems to be with c.address. What am I missing?

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Geoff

 

[Leith Ross]

I simply cannot get the Help example on FindNext to work. Even explicitly
setting the find paramaeters still results in err 91. The example cell value
is changed but then errors out at Loop While.

The problem seems to be with c.address. What am I missing?

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Geoff

Hello Geoff,

You first Find is looking for the number 2. The FindNext is looking
for a 5. If you are searching for all 5's, change the 2 in the first
Find call to a 5,

Sincerely,
Leith Ross

 

[Dave Peterson]

It's a problem with the sample code in VBA's help. In earlier versions, the
code didn't change the .value, it just changed the formatting (font or fill or
something).

In this version, the 2s are changed to 5s. After the last 2 is changed, then
this line:

set c = .findnext(c)
will result in C being nothing -- it wasn't found.

Excel's vba checks both conditions ("Not c is nothing" and "c.address <>
firstaddress") in that "while" statement.

Since c is nothing, then c.address fails.

Personally, I find it easier to just check to see if the code should drop out of
the loop myself:

Option Explicit
Sub testme()
Dim c As Range
Dim FirstAddress As String
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
If c Is Nothing Then
Exit Do
End If
If c.Address = FirstAddress Then
Exit Do
End If
Loop
End If
End With
End Sub

ps. I would also include all the parms on the .find statement. If you don't,
then you'll get the same choices as the user made in the last Edit|Find or the
choices made in last .find in code.

 

[Geoff]

Hi Leith
I don't think that can be.
If you enter 2 in several rows of col A they are all changed to 5 correctly.
The error occurs when no more 2's are found in the range and c becomes
nothing.
How can Nothing have an address is what I cannot fathom.

Geoff

 

[Geoff]

Hi Dave
So my reasoning in my response to Leith was correct - if c is nothing how
can it have an address?

Thanks, I was going spare.

I found if i removed c.address <> firstaddress then it was ok but i think
your solution is more explicit.

Thanks again.

Geoff

 

[Dave Peterson]

And depending on what you're doing with those found cells, you may want to use
the firstaddress check to get out of the loop.

If you're changing formats or just retrieving values, then c would never be
nothing and you'd be stuck forever in that loop. And forever is a long, long
time <vbg>.

 

[Geoff]

Sounds like a cue for a song. <g>

But thanks again, I had spent far too long today trying to get their example
to make sense.

Geoff