I am running a Do loop in which I want to locate all cells in the current
worksheet that have a colon in them.

I am also manipulating these cells as I go, concatenating everything before
the colon to cells below that have leading blanks. My code looks something
like this:

I first tried to put the cells.find function in the do while test thinking
that it would return a value, either boolean or numeric, but that failed.

Then I tried testing the cell location after each find. I made the
assumption that if the application did not find a new cell with the last
find that the activeCell would remain the same.

My problem is that I have not fount the right test to break out of the loop.

Can anyone help. I want to exit the loop when I can't find any other cells
with a colon in them.

=========================
Dim String1, String2, String3 As String
Range("A1").Activate
Cells.Find(What:=":", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
String1 = ActiveCell.Address
String2 = ""
Do While Not (String1 = String2)

Dim SearchString, SearchChar, MyPos As Variant
SearchString = ActiveCell.Value ' String to search in.
' ActiveCell.Value = ""
SearchChar = ":" ' Search for ":".

' A textual comparison starting at position 1.
MyPos = InStr(1, SearchString, SearchChar, 1)
String3 = Mid(SearchString, 1, MyPos - 1)
Do While (Mid(ActiveCell.Offset(1, 0).Value, 1, 1) = " ")
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = String3 + " " + LTrim(ActiveCell.Value)
Loop
String1 = ActiveCell.Address
Cells.Find(What:=":", After:=ActiveCell, LookIn:=xlValues, LookAt:=
_
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
If String1 < ActiveCell.Address Then
'reset String1
String1 = ActiveCell.Address
Else
String2 = ActiveCell.Address
End If

Loop

=========================

Hi

I've modified the loop so that you will breakout after you
have found all the colons. I've also modified a couple of
other places so that I would print something in a cell
that was 1 below the cell with the colon.

HTH

Tony
Dim String1, String2, String3 As String
Range("A1").Activate
Set c = Cells.Find(what:=":", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
String1 = c.Address
String2 = ""
Do 'While Not (String1 = String2)

Dim SearchString, SearchChar, MyPos As Variant
SearchString = c.Value ' String to search in.
' ActiveCell.Value = ""
SearchChar = ":" ' Search for ":".

' A textual comparison starting at position 1.
MyPos = InStr(1, SearchString, SearchChar, 1)
String3 = Mid(SearchString, 1, MyPos - 1)
Do While (Mid(c.Offset(1, 0).Value, 1, 1) = " ")
Range(c).Offset(1, 0).Select

Loop
c.Offset(1, 0).Value = String3 + " " + LTrim
(ActiveCell.Value)
'String1 = ActiveCell.Address
' Cells.Find(what:=":", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
' xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False _
' ).Activate
Set c = Cells.FindNext(c)
' If String1 < c.Address Then
'reset String1
' String1 = ActiveCell.Address
' Else
' String2 = ActiveCell.Address
' End If

Loop While Not c Is Nothing And c.Address <
String1 'Until c.Address = String1
End If


-----Original Message-----
I am running a Do loop in which I want to locate all
cells in the current
worksheet that have a colon in them.

I am also manipulating these cells as I go, concatenating
everything before
the colon to cells below that have leading blanks. My
code looks something
like this:

I first tried to put the cells.find function in the do
while test thinking
that it would return a value, either boolean or numeric,
but that failed.

Then I tried testing the cell location after each find.
I made the
assumption that if the application did not find a new
cell with the last
find that the activeCell would remain the same.

My problem is that I have not fount the right test to
break out of the loop.

Can anyone help. I want to exit the loop when I can't
find any other cells
with a colon in them.

=========================
Dim String1, String2, String3 As String
Range("A1").Activate
Cells.Find(What:=":", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
String1 = ActiveCell.Address
String2 = ""
Do While Not (String1 = String2)

Dim SearchString, SearchChar, MyPos As Variant
SearchString = ActiveCell.Value ' String to
search in.
' ActiveCell.Value = ""
SearchChar = ":" ' Search for ":".

' A textual comparison starting at position 1.
MyPos = InStr(1, SearchString, SearchChar, 1)
String3 = Mid(SearchString, 1, MyPos - 1)
Do While (Mid(ActiveCell.Offset(1, 0).Value, 1,
1) = " ")
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = String3 + " " + LTrim
(ActiveCell.Value)
Loop
String1 = ActiveCell.Address
Cells.Find(What:=":", After:=ActiveCell,
LookIn:=xlValues, LookAt:=
_
xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
If String1 < ActiveCell.Address Then
'reset String1
String1 = ActiveCell.Address
Else
String2 = ActiveCell.Address
End If

Loop

=========================


.