How do you test to break out of a find loop?

  • Thread starter Thread starter Henry Stock
  • Start date Start date
H

Henry Stock

I want to use the following in a Do While Loop, but I don't know how to test
when the value I am searching for is not found.
One additional piece of information. When I do hit on a cell that has this
value I modify the cell so that it no longer has the value I am
Do While (??????)
Cells.Find(What:=":", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
... Test to see if value is found.... Note... If value is not found I
know I can't activate a cell, but I
don't know where the active cell will end up
..... Other Processing ....
Loop
 
Henry
I'm not trying to fob you off but have a look at the help file for find.
I personally find it one of the best references for this type of problem!
;-)
 
Henry,

This should show you a technique

On Error Resume Next
Cells.Find(What:=":", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False) _
.Activate
If Err.Number <> 0 Then
MsgBox "Not Found"
End If
On Error GoTo 0

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I do not want to be impolite either, but yes... I did spend quite a bit of
time trying to find the answer in help, both local and online. I wasted
probably at least few hours looking to no avail, so that is why I am asking
here. I could go into detail about variants I tried.

I tried to test for a result from the find, in many languages functions
return a value, boolean or numeric to indicate sucess or failure. That did
not seem to work.

I tried testing cell addresses before and after to see if the ActiveCell
remained the same or went back to an original location. That did not work.

I did searches on patterns like "Cells.Find", "Find", and others. I
browsed for functions... I looked for info on the Cells object to see if I
could find data on the "Find" method....

I must be a poor searcher if it that easy to find.
 
Hi, Henry. In the VBE Help, the article on the Find Method explains the
method returns Nothing if the text is not found. The linked example shows
how to use it. I've copied the example here:
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
 
Back
Top