Do ... Loop

[Benoit]

Hello,

I'm using the following macro:

Do
Cells.Find(What:="HELP", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Activate
ActiveCell.EntireRow.ClearContents
Loop

Can I add something to the macro to have it stopped once
is can't find any cells with HELP in it???

Thanks!!!

 

[Ron de Bruin]

Hi Benoit

Look in the VBA help (There is a good example)
Try to adapt it and post back if you need help

 

[Kalle]

this is a proper endlessloop.

Do
Cells.Find(What:="HELP", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Activate
ActiveCell.EntireRow.ClearContents
Loop

add result= before cells.find

if result = NOTHING then exit do

 

[Tom Ogilvy]

You mean rather than raising an error?

Dim rng as Range

Do
set rng = Cells.Find(What:="HELP", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
if not rng is nothing then
rng.EntireRow.ClearContents
Else
exit do
End if
Loop

 

[Tom Ogilvy]

I don't think Result = Nothing is a legal statement. Also, if the activate
is left in, it should raise an error when the cells with HELP are exhausted.
Perhaps:

Sub tester3()
Do
Set result = Cells.Find(What:="HELP", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not result Is Nothing Then result.EntireRow.ClearContents
Loop Until result Is Nothing

End Sub


Was more what you had in mind.