Stopping Find from wrapping around in code?

[Maury Markowitz]

I'm using Find to loop through column ET on my sheet and find all the
"2"s. I stop the search when the .Row of the last .FindNext is past
the last row of the sheet.

But it never is. That's because when it actually does find the last
one, the next .FindNext wraps back to the top of the column again!

Is there some way to stop it? The Help doesn't seem to suggest a
solution.

Maury

 

[Jim Thomlinson]

You need to store where you found the first instance and then stop looping
when you get back to there... Code similar to this should do the trick...

Sub FindStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundall As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Range("A1:A100")
Set rngFound = rngToSearch.Find(What:="this", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
strFirstAddress = rngFound.Address
Set rngFoundall = rngFound
Do
Set rngFoundall = Union(rngFound, rngFoundall)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundall.EntireRow.Select
End If
End Sub

 

[Peter T]

In Help search for "Find Method", then look at the example

Regards,
Peter T

 

[Maury Markowitz]

Ok, fair enough, but here's another...

Am I right in thinking that you cannot find in hidden columns?
Whenever I try to do a ast.Range("ET:ET").Find... after hiding the
columns, it fails and tells me the With block is not set.

Maury

 

[Maury Markowitz]

In Help search for "Find Method", then look at the example

Umm, I specifically mentioned that I looked in the Help example.

Maury

 

[Peter T]

You didn't specifically mention you looked in the help example. This is what
you said -
"The Help doesn't seem to suggest a solution."
which I took to mean for some reason you were unable to find the example
which, as far as I can see, answers your original question. In case you
didn't find it here it is -

Find Method Example

This example finds all cells in the range A1:A500 on worksheet one that
contain the value 2, and then it makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Regards,
Peter T

In Help search for "Find Method", then look at the example

Umm, I specifically mentioned that I looked in the Help example.

Maury

 

[john]

someone correct me if I am wrong but I believe there is an error with the
code construct of the Find Method help file example (2003)? even if not, I
did have problems with the example and now use modified version as follows.

Dim c As Range
Dim FirstAddress As String
With Worksheets(2).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
If c Is Nothing Then Exit Do
Loop Until c.Address = FirstAddress
End If
End With


hope of some interest.

 

[Jim Thomlinson]

Post your full code...

 

[Peter T]

I tried your example exactly as posted with some 2's in column A on the
second sheet. It worked fine for me (though it wouldn't if the column was
hidden)

Regards,
Peter T

 

[john]

Hi Peter,
thanks for feedback & good point about hidden columns.