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

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

--
HTH...

Jim Thomlinson


"Varne" wrote:

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

Or the non-looping way:

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


"Varne" wrote in message
...
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

Hi Jim

Your codes do the job. Thanks.

Varne

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Varne" wrote:

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