Calling .Find within .Find operation in Excel VBA 1

[sjh]

Hi,

The first FIND operation looks for a string in sheet 1, and the second FIND operation which is within the DO-While loop of the first FIND looks for a string in sheet 2.

When I try to run this code, it doesn't seem to work. It fails on the line with '*****. Is it because I switch back and forth between sheet 1 and sheet 2?

Thank you for your help.

Here's a snippet of my code.

firstSheet.Activate
firstSheet.Range("D1&quot.Select
With firstSheet.Range("D&quot
Set rFind = .Find(What:=sFirstSearch, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not rFind Is Nothing Then
Do
firstAddress = rFind.Address

''''''''
secondSheet.Activate
'Do Second FIND in secondSheet
''''''''

firstSheet.Activate
Set rFind = .FindNext(rFind) '*****
Loop While Not rFind Is Nothing And rFind.Address <> firstAddress
End If
End with

 

[PHV]

What is the error code and description of the failure ?
Anyway, IMHO the assignment of firstAddress should be done before entering the loop.

 

[VBAjedi]

Ok. Find is a bit like the Clipboard: it has a system-assigned memory to remember the last Find you did. You have two finds here - call them Find1 and Find2. Find1 executes, then you switch worksheets and execute Find2. When Find2 executes, it clears the settings from Find1 out of the memory. So when you try to call Find1's &quot;FindNext&quot; method, the settings that are in the memory are for Find2. Bottom line: if you use .FindNext with nested Finds, the code breaks.

I would suggest trying a normal Find instead of FindNext for Find1. To make sure it picks up where the last Find1 left off, you need to set the &quot;After&quot; property of the find to rFind (the found cell of the first Find1).

It will take some tweaking to get the loop to work, though. . .

VBAjedi