Making Find go through worksheet only once
 

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

Forgot to mention... using Excel 2000.

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

Frank Marousek wrote:

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

--

Dave Peterson

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


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