Can .FindNext return Nothing??

[Wild Bill]

Its wraparound nature suggests it would never return Nothing (assuming
that .Find itself didn't return nothing). Besides, the XL03 FindNext
Method help instructs you to test its returned address. So why check
for Nothing?

Moreover, why does their help show this?
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress

Why test if c is Nothing *AND* do the c.address wraparound test? Is
there an occasion where the wraparound doesn't happen? (I could see this
perhaps only if .Find failed; but that's not the case in the help text.)


In testing I can never get FindNEXT to return Nothing. Only FIND.

Separate question: the "After" argument help text in XL03 says "If this
argument isn’t specified, the search starts after the cell in the
upper-left corner of the range." Am I high, or shouldn't that say,
"starts after the active cell" ??

 

[Doug Glancy]

Wild Bill,

'Am I high, or shouldn't that say, "starts after the active cell" ??'

If by that you mean, is the activecell always in the upper left corner of
the range, I don't think so. The Find is not related to the Activecell
(unless you generate the code form the Recorder). If it is generated from
the Macro Recorder and you select a range before the Find, and you drag from
the bottom up or from right to left, the Activecell won't be the upper left
cell. Here's code recorded by dragging from lower right to upper left:

Range("C39").Select
Range("D9").Activate

I'm not high, but I may have totally missed your point here. But I had fun
thinking about it.

Doug

 

[Dave Peterson]

Actually, I think the xl2003's help has the opposite bug that you describe.

I think that it won't wraparound to the first cell. The sample code is looking
for "2" and changes it to "5". So after it gets all the 2's, the .findnext will
return nothing.

And that causes a blowup in this portion:

Loop While Not c Is Nothing And c.Address <> FirstAddress

Since c is nothing, c.address doesn't make sense (and kablewie!!!).

If I recall correctly, an earlier version of this code didn't change the value
from 2 to 5. It colored the cell (or changed the formatting somehow).

Then the code would indeed wrap around. Now your question is more appropriate
(and I don't have a guess why they tested for nothing.)

 

[Vasant Nanavati]

and kablewie!!!

Hi Dave:

I'm getting a bit tired of having to make these technical corrections to
your posts, but ...

It's "kablooie" or "kablooey;" not "kablewie!" <g>

On Yahoo! search, I got 11,000 hits for the first spelling, 34,000 for the
second, and 54 for your variation (along with the helpful hint: Did you mean
"kibblewhite?").

Regards,

Vasant
<gd&r>

 

[Dave Peterson]

I once got a message asking what kable-wie (pronounced cable-wee/why) meant.

(And I can find lots of posts in the *excel* newsgroups which use that same
spelling <vbg>.)

 

[Vasant Nanavati]

You know, it's funny but that was exactly my reaction the first time I saw
you use it a few years ago ... "What the heck is a cable-wee?"

 

[Wild Bill]

Thanks for responding. Here's what I meant:
Make a 2X2 rectangle at A1 and put 2 in each cell (on virgin worksheet).
Highlight A1, and go shift-ctl-end to mark all 4 cells.
A1 is now the active cell; selection is A1:B2.
Now Ctl-F for 2 (hit enter key).
A2 is now ActiveCell.
Doesn't Findnext now proceed from the ActiveCell, rather than as Help
states? Thanks for correcting me if I'm wrong here.

If by that you mean, is the activecell always in the upper left corner of
the range, I don't think so. The Find is not related to the Activecell

Wild Bill wrote earlier:

 

[Wild Bill]

Ah, very astute of you to pick that up! So c indeed could become
Nothing if c.Value is modified. Way to go! You're definitely underpaid
for what you do here :-O

It also occurred to me that break mode (i.e., stopping in Debug window)
could allow an action to result in a FindNext failure(e.g. changing
ActiveSheet), but I felt that the Nothing test in their example should
not have been written to contemplate that. Only real men would step
code, right, and they ought to be big enough to deal with such
consequences on their own time!

Now going beyond my original question, as you have established that
Nothing indeed is possible: as to kablewie, so much for left to right
evaluation in VBA - NOT! Boo! Shame on them! And since that happens, are
you saying that thus it is useless to test a range (cell) c for Is
Nothing, when c.address is in the same If? So their example should have
tested for nothing inside the loop and Exit Do, and let the While only
test the .Address?

 

[Wild Bill]

If I recall correctly, an earlier version of this code didn't change the value
from 2 to 5. It colored the cell (or changed the formatting somehow).

Then the code would indeed wrap around. Now your question is more appropriate
(and I don't have a guess why they tested for nothing.)

Here is the Access 97 example:
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[Wild Bill]

Here's an interesting result from A97. Can you explain?
Put a 2 in the 4 cells A1:B2. Go Ctl-Home, shift-Ctl-End.
Sub foo()
Dim c As Range, rng As Range, firstaddress As String
Set rng = Range("A1:A2")
Set c = rng.Find(2)
firstaddress = c.Address
Do
c = 5
Set c = rng.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End Sub

A1 and A2 change to 5, and then kabloomationationatory stench erupts.
I can make so sense of this...

 

[Zack Barresse]

There would be no need to test for it inside of the loop. That's why it's
kept as the loops *condition* to be in it's running/looping state. That's
like trying to figure out which came first, the egg shell or the chicken..
it does make a man think though ..

So you've established that the Range Object can in fact be Nothing if there
are no more of the search/Find values left in the range to search in, in
other words they have all been modified in some fashion. The other thing
you need to make certain of is that Find/FindNext will (by default) start
from the upper-left portion of the range to search.

So both checks, for Is Nothing and if the .Address <> FirstAddress (barring
it's been set), are necessary for a complete check and to cover all your
bases.

The only other question may be would you use ...

Do Until c.Address = fAddy Or c Is Nothing
'...
Loop

... or..

Do
'...
Loop Until c.Address = fAddy Or c Is Nothing

I recommend you try each one for yourself. You will see that in one of
these, it will look at both the Do and the Loop lines, and the other one
will only look at the Do line on the first iteration.

Good thread.

 

[Doug Glancy]

Bill,

I agree with what you said. The help text that you quoted has to do with
the behavior when Find is called from VBA. If you alter the help example a
bit, like below, it will go act as you described.:

Sub test()
Dim c As Range, firstAddress
Dim i As Long
i = 0
With Worksheets(1).Range("a1:b2")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Debug.Print firstAddress
Do
Set c = .FindNext(c)
Debug.Print c.Address
i = i + 1
Loop While i < 10 And Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

But if I remove the "After" argument from "Findnext":

Set c = .FindNext()

it just keeps finding B1, which is as the help text describes.

ActiveCell doesn't enter into it - when called from VBA - it doesn't matter
where the Activecell was before you do the Find, it's unchanged and has no
effect on the Find, so I wouldn't expect VBA help to mention it.

Unless of course, I'm wrong ...

Doug

 

[Zack Barresse]

Check what you set your "rng" range object to ...

.... kabloomskidaisy ...

 

[Wild Bill]

Doooooooooh

 

[Wild Bill]

You're quite right - thank you. Careless of me. Well it would help if
Wild Bill actually tried it in VBA rather than assume that the default
VBA action mimicked the sheet edit Find!

(Actually, in some of my tests I had the arg as c but the disturbed
voices in my head said there was no argument there - for the benefit of
any mental health professionals that read this :-O )

 

[Wild Bill]

There would be no need to test for it inside of the loop.
(...)
So both checks, for Is Nothing and if the .Address <> FirstAddress (barring
it's been set), are necessary for a complete check and to cover all your
bases.

But what we're seeing is that the code bombs when c really is Nothing.

It's similar to this deal:
A1=NA()
B1=IF(OR(ISNA(A1),A1=0),1,0)

You'd like it to give 1, but it gives #N/A

 

[Dave Peterson]

I guess I'm saying that you should be aware of what you're doing. If you don't
change the value, I can't see why checking for nothing is required.

But if you get rid of it (change the value or even delete the cell), then
checking for Nothing makes sense.

But if I wanted to check both, I'd do something like:

do
.....

if c is nothing then
exit do
else
if c.address = firstaddress then
exit do
end if
end if

loop

 

[Dave Peterson]

That looks like the one that was in Excel97, too <bg>.

 

[Dave Peterson]

Ps. Thanks for posting the sample code.

That looks like the one that was in Excel97, too <bg>.

 

[Wild Bill]

Looks like I picked a bad day to stop sniffing glue!