Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Forgot to mention... using Excel 2000.
|
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help! making a worksheet more automated? | Excel Discussion (Misc queries) | |||
Worksheet Function - Find? | Excel Worksheet Functions | |||
How Do I Find A Specific Worksheet | Excel Discussion (Misc queries) | |||
Need formule which would find cell (exmp b3) value on another worksheet! please help! | Excel Worksheet Functions | |||
Need find cell b3 value on another worksheet! | Excel Worksheet Functions |