understanding the .FIND example

[AlanC]

I am having difficulty in understanding the .FIND example in the help files.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

This works fine but in my case I need to do some reordering of rows and then
continue to FINDNEXT. There appears to be a pointer set within the FIND
method which is where the .Findnext(c) starts. My changes mean that the
pointer is set to a row before the found occurrence and thus the .Findnext
repeats
or
at the end of the range the repeat finds a different address for the
c.address from that stored in firstaddress so loops continuously.

Is the pointer addressable, modificable or am I going about it the wrong way?

 

[OssieMac]

Hi Alan,

Find next will give problems if you re-order your data after the initial
find and before Find next. Other options are to repeat the find line of code
instead of using Find next. Include 'After' parameter which will be the
address of the last find after the re-ordering of the data.

As a further tip, it is recommended that you include all of the parameters
in Find because Excel remembers them from the previous use even if it was in
the interactive mode. Use Record Macro to find the parameters to include. You
will have to modify the code a bit to use it in the same way that you have in
your example but it is an easy way to get the parameters right.

 

[JLGWhiz]

To do what you have described you would have to insert code between the
firstAddress = c.Address and the Do lines. In that event, the FindNext sort
of becomes superfluous. If you only intend to rearrange the rows once, then
do the simple Find part of the code. If you intend to rearrange the rows on
each iteration then put the Find function inside a loop . However, if you do
that, you might find that it stops at the same place each time because of the
reorganization of the rows.

On the other hand, as long as none of the rows above the first cell found
are changed, or above any subsequent cell found, it should not matter and the
code should work OK.

In short, changing rows above the found cell will change the actual cell
location but will not change the variable value of firstAddress. Changing
rows beneath the found cell will have no effect.

 

[Wild Bill]

Reordering is brazen in the middle of a .Find loop, For loop, and
especially For Each. I would not consider reordering amidst those
processes; even if I got away with it, it would be a portability gamble
since the internal pointer work is subject to the application's
under-the-hood (and undocumented) whimsies.

Consider successively calling the .Find & FindNext in a routine with a
boolean that indicates whether to reorder following the .Find. Exit out
before FindNext-ing for the reordering case.

 

[Tim Zych]

Maybe you can set a range equal to the found cells, and then perform the
modifications after everything is found.

Sub Tester1()
Dim FoundRng As Range
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
Set FoundRng = c
firstAddress = c.Address
Do
' c.Value = 5
Set c = .FindNext(c)
Set FoundRng = Union(FoundRng, c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Dim FoundCell As Range
For Each FoundCell In FoundRng.Cells
FoundCell.Value = 5 ' or whatever
Next

' or in one fell swoop
' FoundRng.Value = 5
End Sub

Find/FindNext gives a granular level of control, but if you need to just
replace 2's with 5's you can use the Replace function

Range("A1:A500").Replace What:=2, Replacement:=5, LookAt:=xlWhole ', etc

One thing..both Find and Replace use the last-used values for unspecified
parameters, so if the macro should find using part of the cell (xlPart), or
the whole cell (xlWhole), specify it in the parameter LookAt. Otherwise, if
someone runs the macro after they manually used Find looking at the Whole
cell (or if another macro did it), that's what the next macro will do too,
unless the macro specifies the LookAt parameter. See the help file for other
parameters, such as MatchCase, etc.