Stopping looped "Find" command
 

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

Stopping looped "Find" command
 

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

Stopping looped "Find" command
 

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

Stopping looped "Find" command
 

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