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" ??

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("C3:D9").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

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

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.

On Fri, 29 Apr 2005 17:28:39 -0700, "Doug Glancy"
wrote:

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:
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" ??

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

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

On Fri, 29 Apr 2005 17:28:39 -0700, "Doug Glancy"
wrote:

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:
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" ??

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 )

On Fri, 29 Apr 2005 21:34:03 -0700, "Doug Glancy"
wrote:

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

Set c = .FindNext()

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

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.)

Wild Bill wrote:

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" ??

--

Dave Peterson

"Dave Peterson" wrote in message
...

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

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 wrote:

"Dave Peterson" wrote in message
...

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

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?"

--

Vasant


"Dave Peterson" wrote in message
...
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 wrote:

"Dave Peterson" wrote in message
...

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

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?

On Fri, 29 Apr 2005 20:37:12 -0500, Dave Peterson
wrote:

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!!!).

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. :)

--
Regards,
Zack Barresse, aka firefytr


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

On Fri, 29 Apr 2005 20:37:12 -0500, Dave Peterson
wrote:

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!!!).

On Fri, 29 Apr 2005 20:33:34 -0700, "Zack Barresse"
wrote:
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

From what I understand, there needs to be an On Error of sorts, because
you're going to be testing for c.Address, and if c Is Nothing, then it
doesn't have an address property. I believe that is the error you are
receiving. Um, did I miss something again? Or is that what you are talking
about? (Wouldn't be the first time ... )

--
Regards,
Zack Barresse, aka firefytr


"Wild Bill" wrote in message
...
On Fri, 29 Apr 2005 20:33:34 -0700, "Zack Barresse"
wrote:
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

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

Wild Bill wrote:

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?

On Fri, 29 Apr 2005 20:37:12 -0500, Dave Peterson
wrote:

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!!!).

--

Dave Peterson

Hear, hear! Your contribution here is and has been invaluable to that
end. Muchos, muchos gracias.

I guess I'm saying that you should be aware of what you're doing.

On Fri, 29 Apr 2005 20:37:12 -0500, Dave Peterson
wrote:

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

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

Wild Bill wrote:

On Fri, 29 Apr 2005 20:37:12 -0500, Dave Peterson
wrote:

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

--

Dave Peterson

Ps. Thanks for posting the sample code.

Dave Peterson wrote:

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

Wild Bill wrote:

On Fri, 29 Apr 2005 20:37:12 -0500, Dave Peterson
wrote:

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

--

Dave Peterson

--

Dave Peterson

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

On Sat, 30 Apr 2005 07:30:02 -0500, Dave Peterson
wrote:

Ps. Thanks for posting the sample code.

Dave Peterson wrote:

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

Wild Bill wrote:
Here is the Access 97 example:

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...

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

.... kabloomskidaisy ... ;)


--
Regards,
Zack Barresse, aka firefytr


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

Doooooooooh

On Fri, 29 Apr 2005 22:18:13 -0700, "Zack Barresse"
wrote:

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

... kabloomskidaisy ... ;)