.Findnext: wrap, or == Nothing ??

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Which is it? XL2002 Help is contradictory. They test a .Findnext loop against Nothing; yet they say (as we all know) that it DOES wrap around.

Is what really happens that you get "one Nothing per wraparound?"

Note: I have been able to force a Nothing, and you can too, using what I perceive as an XL2002 bug:
merge b2 to c3. Type FOO in B2.
put cursor on a1
Ctrl-F
Type FOO
Hit enter twice.

Merged cells and finds are certainly a nightmare. Anyway, is there any other way that you can get Nothing on findnext?

(Well, I can think of another - moving off the active sheet, or selecting an unmatching range, and going shift-F4! Is their test against nothing simply to address things like that?)
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
I presume you are following the example in VBA Help under the topic Find Method. The variable c will be nothing if the value of c is changed as part of the loop code. However testing against the address will exit the loop so testing whether c is nothing in the loop while line is superfluous.

In fact if c is nothing you will get a runtime error 91 object variable not set.

Code:
Sub test()
With Worksheets(1).UsedRange
    Set c = .Find(5, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 43
            Set c = .FindNext(c)
            If c Is Nothing Then MsgBox "c is nothing"
        'The following line will produce an error
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Ah, modifying c between the .Find and .Findnext - talk about tampering with mother nature!

Okay, I guess I get it - the Nothing test in the help example was superfluous only for their example. And it IS meaningful if the active region changes, the search range is modified, or if there's a merged cell.

Thanks
 

Forum statistics

Threads
1,148,391
Messages
5,746,435
Members
424,017
Latest member
jaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top