Stopping looped "Find" command

[Guest]

Hi

I have the following Excel problem.

I have the following details in a column.

teka sfffss
fhhr teka
25 teka

I used the following looped 'find' command to make all of them 'teka'.


Cells(1, 1).Select

Do
Cells.Find(What:="teka", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell = "teka"
Loop Until Not ActiveCell.FormulaR1C1 = "teka"
End Sub

The problem is the above one is an infinite loop. How to stop it ? If I am
to use error handler how to use it more once in one macro?

Thanks

Varne

 

[Guest]

Try something like this...

Sub test()
Dim rngFound As Range
Dim strFirstAddress As String

Set rngFound = Cells.Find(What:="teka", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Teka was not found"
Else
strFirstAddress = rngFound.Address
Do
rngFound.Value = "teka"
Set rngFound = Cells.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress

End If
End Sub

 

[Tim Zych]

Or the non-looping way:

Cells.Replace What:="*teka*", Replacement:="teka", LookAt:=xlPart,
MatchCase:=False

 

[Guest]

Hi Jim

Your codes do the job. Thanks.

Varne

Try something like this...

Sub test()
Dim rngFound As Range
Dim strFirstAddress As String

Set rngFound = Cells.Find(What:="teka", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Teka was not found"
Else
strFirstAddress = rngFound.Address
Do
rngFound.Value = "teka"
Set rngFound = Cells.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress

End If
End Sub