Nesting Find and FindNext

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

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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com


> 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

****,

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 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, ****.

> 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, ****.

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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

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


"**** Kusleika" <dkusleika@gmail.com> wrote in message
news:ORnNxCPnFHA.3656@TK2MSFTNGP09.phx.gbl...
> 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.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
>
>> 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
>
>

****,

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

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

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Many thanks ****, I'll give it a whirl



"**** Kusleika" <dkusleika@gmail.com> wrote in message
news:%23qPtB5anFHA.2904@TK2MSFTNGP14.phx.gbl...
>> 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.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
>