problem with Find Method - searchorder:=xlByColumns

[brettdj]

I've been using a simple Find routine to highlight cells that have precedents to others sheets. After some code tweaking the routine found itself in an infinite loop (in one file only), when I debugged it I found that the first found cell (First_Address) was not found again. There was nothing special about this cell, it wasn't merged etc.

When I added the optional searchorder:=xlByRows then the first found cell was picked up but then if I tried searchorder:=xlByColumns it was skipped again. I assume the xlByColumns had been used earlier and was saved as the default parameter

Any ideas why the xlByColumns parameter caused this problem?

Cheers

Dave

 Set myrange = Nothing
 Set TestRange = Nothing

 On Error Resume Next
 Set TestRange = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
 On Error GoTo 0

With TestRange
                Set C = .Find(SheetString, LookIn:=xlFormulas, LookAt:=xlPart, searchorder:=xlByRows, MatchCase:=False)
                If Not C Is Nothing Then
                    First_Address = C.Address
                    If myrange Is Nothing Then Set myrange = C    ' need to start union
                    Do
                        Set C = .FindNext(C)
                        Set myrange = Union(myrange, C)
                    Loop Until C.Address = First_Address
                End If
End With

 

[BrianB]

I am not sure, but you might need
If myrange Is Nothing Then Set myrange = ActiveSheet.Range(C.address)