Find errors

[John]

We are experiencing intermittent errors from "Finds" in
our macros. Generally these are when our macros have
performed over 200 searches. The Find suddenly fails
although if the search is performed on the worksheet it
achieves the find successfully. So while the data is
present, the search appears to have grown tired!

Is there a limit to the number of Finds allowed, or do
some buffers need clearing or is there some other solution
other than running the macro in sections to reduce the
volume of searches?

Thanks

 

[Tom Ogilvy]

There is no limit that I have heard of.

Perhaps the conditions for the find are not being set explicitly and causing
the failure.

 

[Dave Peterson]

Do you have merged cells in your worksheet?

Are you looking for a value in the merged cell?

In xl2002 (and I think xl2k), this fails after the 2nd find.

Option Explicit
Sub testme()
Dim FoundCell As Range

Worksheets.Add
Range("c4:d11").Merge
Range("c4").Value = "test"
Range("g2:i10").Merge
Range("g2").Value = "test"

Do
Set FoundCell = Cells.Find("test", after:=ActiveCell, _
lookat:=xlPart, LookIn:=xlValues, searchdirection:=xlNext)
If FoundCell Is Nothing Then
MsgBox "it blew up"
Exit Do
End If
FoundCell.Activate
Debug.Print FoundCell.Address
Loop

End Sub


If I comment out the .merge lines, then it'll become an endless loop and I need
to stop it with ctrl-break.

(I think xl97 didn't have this problem with merged cells and find.)