Nesting Find and FindNext

[SA3214]

Is it possible to have nested finds

Find(i)
Find (p)
Do
FindNext(p)
Loop
FindNext(i)

Apologies for the mangling of the code but I hope you get the gist

Regards & TIA

 

[Dick Kusleika]

No, it's not possible. Every time you perform a Find, the data that would
make FindNext work is erased. It's just a limitation of the Find method.
For Find(p) you'll need to loop through the cells if you want FindNext(i) to
work.

 

[davidm]

Dick,

Can you please, elaborate. I am piqued.

Suppose I have: "Apple" "OX" "Mule" "Apple" "Soap" "OX" "OX
in Range("A1:A7") and I nominate "OX" to be tracked down in the rang
using FIND/FINDNEXT loop. My understanding of the Find/findnex
construct is that at the first pass of loop, FIND will hit a blank at
"Apple" but will proceed and locate a match in "OX". Then the FINDNEX
command takes over, again seeking a match for "OX" which will only b
met 4 passes further down at A6.

In all this, where do we fit *the data that would make FindNext wor
is erased*. May be I am being obtuse, Dick

 

[Dick Kusleika]

Suppose I have: "Apple" "OX" "Mule" "Apple" "Soap" "OX" "OX

" in Range("A1:A7") and I nominate "OX" to be tracked down in the
range using FIND/FINDNEXT loop. My understanding of the Find/findnext
construct is that at the first pass of loop, FIND will hit a blank at
"Apple" but will proceed and locate a match in "OX". Then the FINDNEXT
command takes over, again seeking a match for "OX" which will only be
met 4 passes further down at A6.

In all this, where do we fit *the data that would make FindNext work
is erased*. May be I am being obtuse, Dick.

No, I just probably explained it poorly. Assume you have data in a grid
like

1 2 5 7 8 5
10 3 6 9 2 8
1 5 9 1 1 6

Further assume that every time there's a 1 in column A, any five that exists
in that row needs to be changed to 50. It would be nice to write code like
this

Set rFound1 = Range("A1:A3").Cells.Find(1)

Do Until rFound1 Is Nothing Or 'Some test to catch wrap around
Set rFound2 = rFound1.EntireRow.Cells.Find(5)
Do Until rFound2 Is Nothing
rFound2.Value = 50
Set rFound2 = rFound1.EntireRow.FindNext(rFound2)
Loop
Set rFound1 = Range("A1:A3").FindNext(rFound1) '[1]
Loop

[1] This line won't work because the original Find's information has been
lost. VBA won't know to search for '1' anymore because another Find has
been executed in the interim. In fact, it will search for '5' in A1:A3 and
the '5' in B3 will never get changed to 50.

 

[SA3214]

OK ... that explains why it hasn't worked for me ...
Would it work if I stored the cell.address of each successful find of (i) as
say 'last_ i_add'
and then, instead of using FindNext(i)
I used Find (i) with the option After 'last_i_add' .... similar to code
below

TIA
(I've said it before and I'll say it again ....you guys are amazing)

Last_i_Add="A1"

Set i = .Find(varA, lookin:=xlValues, )
If Not i Is Nothing Then
First_i_Add = i.Add
Last_i_Add = i.Address
Set p= Find(varP, lookin:=xlValues,)
If Not p is Nothing Then
Do
Set p= FindNext(varP)
If Not p is Nothing Then
Code
End If
Loop While Not p Is Nothing And p.Address <>
first_p_Address
End if
End If

'using 'After:=last_i_Add' for subsequent finds of varA
Do
Set i = .Find(varA, lookin:=xlValues, After:=Last_i_Add )
If Not i Is Nothing Then
Last_i_Add = i.Address
Set p= Find(varP, lookin:=xlValues,)
If Not p is Nothing Then
Do
Set p= FindNext(varP)
If Not p is Nothing Then
Code
End If
Loop While Not p Is Nothing And p.Address <>
first_p_Address
End if
End if
Loop While Not i Is Nothing And last_i.Address <> first_i_Address

 

[davidm]

Dick,

Many thanks for the profound explanation. Makes might sense to me
now.

 

[Dick Kusleika]

OK ... that explains why it hasn't worked for me ...

Would it work if I stored the cell.address of each successful find of
(i) as say 'last_ i_add'
and then, instead of using FindNext(i)
I used Find (i) with the option After 'last_i_add' .... similar to
code below

Yes, that would work. I didn't check the syntax, but the idea is solid.

 

[SA3214]

Many thanks Dick, I'll give it a whirl

Yes, that would work. I didn't check the syntax, but the idea is solid.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com