Help with Loop Until

[toesparkle]

Hi all!
I need to stop a Do Loop function, but can't figure out how. I am
finding the word "Open", replacing it with text from another cell, and
looping until all the "Open"s are gone.

This is probably an easy fix, but it's kicking my butt.
I'd appreciate any help you can give me.
Thanks!
Samantha

Do
Cells.find(What:="Open", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Offset(0, 1).Select
Application.CutCopyMode = False
Selection.Copy
Selection.Offset(0, -1).Select
ActiveSheet.Paste
On Error Resume Next
Loop Until ?

 

[bhofsetz]

Samantha,
Are you wanting to check the entire sheet for "Open" ? If so then
you don't even need the do loop. It will check all cells then stop.

If you only want to loop through certain cells then you want to use
either a

Do Until
...
Loop

or a

Do While
...
Loop

structure where the condition you need satisfied follows the Do
statement

You could also use an

If [your criteria is met] Then Exit Do

statement

Hope This Helps

 

[Tom Ogilvy]

Dim rng as Range
set rng = Cells.find(What:="Open", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
if not rng is nothing then
do
rng.Value = rng.offset(0,1),Value
set rng = cells.FindNext(rng)
Loop Until rng is nothing

End if

 

[Lee Hunter]

Hi Samantha,

You've got apples and oranges going here.

If you want to use a DO loop and control the loop with Selection.offset then
just use a simple If statement inside the loop.

If you want to use the FIND function, you must set up a range variable for
the FIND to work within, then test the result of the Find function.

I think you'll find the Do If method easier.

Hope that helps you.

Lee Hunter

 

[Jim Thomlinson]

Give this a try

Sub FindOpen()
Dim wksToSearch As Worksheet
Dim rngTosearch As Range
Dim rngFirst As Range
Dim rngCurrent As Range

Set wksToSearch = ActiveSheet
Set rngTosearch = wksToSearch.Cells
Set rngCurrent = rngTosearch.Find("Open")

If rngTosearch Is Nothing Then
MsgBox "Not Found"
Else
Set rngFirst = rngCurrent
Do
rngCurrent.Offset(0, 1).Copy rngCurrent
Set rngCurrent = rngTosearch.FindNext(rngCurrent)
If rngCurrent Is Nothing Then Exit Do
Loop Until rngFirst.Address = rngCurrent.Address
End If
Set wksToSearch = Nothing
Set rngTosearch = Nothing
Set rngCurrent = Nothing
Application.CutCopyMode = False
End Sub

 

[Tom Ogilvy]

There was a small typo in rng.offset(0,1).value (had a comma in place of
a period).

this has been lightly tested to demonstrate that find works just FINE

Sub AB()
Dim rng As Range
Set rng = Cells.Find(What:="Open", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rng Is Nothing Then
Do
rng.Value = rng.Offset(0, 1).Value
Set rng = Cells.FindNext(rng)
Loop Until rng Is Nothing

End If

--
Regards,
Tom Ogilvy

Dim rng as Range
set rng = Cells.find(What:="Open", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
if not rng is nothing then
do
rng.Value = rng.offset(0,1),Value
set rng = cells.FindNext(rng)
Loop Until rng is nothing

End if

--
Regards,
Tom Ogilvy



in message news:[email protected]...

 

[Jim Thomlinson]

Actually that is the trick with find / find next. It is an infinite loop. Try
it. You need to set a stop condition. I used 2 stop conditions in mine for
just that reason. If the copy function was pasting the word "Open" then it
would be infinite. If it was not pasting the word open then it would run out
of "Opens" to search for and possibly crash. Tom's covers off the most likely
scenario in that it will run out of "Opens" to search for. If that is the
case then Toms' is more efficient than mine. If not then Tom's will possibly
run infinitly.

 

[Tom Ogilvy]

I am

finding the word "Open", replacing it with text from another cell, and
looping until all the "Open"s are gone.

If the code is replacing all the opens, then there isn't much danger of
that - and replacing all the opens was the point of the exercise. Sheesh!

--
Regards,
Tom Ogilvy

Actually that is the trick with find / find next. It is an infinite loop. Try
it. You need to set a stop condition. I used 2 stop conditions in mine for
just that reason. If the copy function was pasting the word "Open" then it
would be infinite. If it was not pasting the word open then it would run out
of "Opens" to search for and possibly crash. Tom's covers off the most likely
scenario in that it will run out of "Opens" to search for. If that is the
case then Toms' is more efficient than mine. If not then Tom's will possibly
run infinitly.
--
HTH...

Jim Thomlinson

Samantha,
Are you wanting to check the entire sheet for "Open" ? If so then
you don't even need the do loop. It will check all cells then stop.

If you only want to loop through certain cells then you want to use
either a

Do Until
...
Loop

or a

Do While
...
Loop

structure where the condition you need satisfied follows the Do
statement

You could also use an

If [your criteria is met] Then Exit Do

statement

Hope This Helps

 

[toesparkle]

All,
Thanks so much for the help. I used Tom's suggestion and it worke
like a charm. I really appreciate the time and effort you all put int
helping those of us who don't get it as well as you do.

Thanks again!
Samanth