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

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 Stock, Network Administrator
onProject.com
3 Wing Drive
Cedar Knolls, NJ 07927-1006

How do you test to break out of a find 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 Stock wrote in message
...
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 Stock, Network Administrator
onProject.com
3 Wing Drive
Cedar Knolls, NJ 07927-1006

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

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)

"Henry Stock" wrote in message
...
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 Stock, Network Administrator
onProject.com
3 Wing Drive
Cedar Knolls, NJ 07927-1006

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

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.

"Loomah" wrote in message
...
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 Stock wrote in message
...
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 Stock, Network Administrator
onProject.com
3 Wing Drive
Cedar Knolls, NJ 07927-1006

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

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 he
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 WithHTHEd"Henry Stock" wrote in message
...
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.

"Loomah" wrote in message
...
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 Stock wrote in message
...
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 Stock, Network Administrator
onProject.com
3 Wing Drive
Cedar Knolls, NJ 07927-1006