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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,089
Members
414,501
Latest member
mdhaumyu

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