Making Find go through worksheet only once

F

Frank Marousek

Is there a way to make Excel search through a worksheet only once when using
the Find command. As it is, it will loop around and around endlessly until I
realize that it's gone around more than once and cancel the search. In a
large worksheet, this is really a pain because it's hard (sometimes
impossible) to tell when and if it's looped around. Ideally I would like it
to stop at the same point at which it started, but I would settle for
stopping at the end of the worksheet.

Thanks
 
D

Dave Peterson

Remember to look at the namebox (to the left of the formula bar). And watch the
vertical scrollbar. When you're near the top, you've looped.

You may want to try Jan Karel Pieterse's addin FlexFind:
http://www.oaltd.co.uk/MVP/

You can do a find all and have a little more control.

(xl2002+ also has a FindAll button.)
 
A

Alan Beban

Frank said:
Is there a way to make Excel search through a worksheet only once when using
the Find command. As it is, it will loop around and around endlessly until I
realize that it's gone around more than once and cancel the search. In a
large worksheet, this is really a pain because it's hard (sometimes
impossible) to tell when and if it's looped around. Ideally I would like it
to stop at the same point at which it started, but I would settle for
stopping at the end of the worksheet.

Thanks
Click to expand...
You might want to consider something like

Sub xy10000()
Dim rng As Range, x As Range, i As Long
Set rng = Range("A1:A20")
Set x = rng.Find(what:=2)
Debug.Print x.Address
For i = 2 To Application.CountIf(rng, 2)
Set x = rng.FindNext(Range("" & x.Address & ""))
Debug.Print x.Address
Next
End Sub

Alan Beban
 
Top