problem with Find Method - searchorder:=xlByColumns

brettdj

Active Member
Joined
Feb 5, 2003
Messages
426
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


Code:
 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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
I am not sure, but you might need
If myrange Is Nothing Then Set myrange = ActiveSheet.Range(C.address)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,941
Messages
5,856,425
Members
431,812
Latest member
Elastomers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top