MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formatting text using activecell offset

Posted by Rob Allen on October 03, 2001 12:42 AM

I need to take a text or number from a cell in a column and then add certain text symbols.
e.g. A1 = rallen = :"rallen",
So i need it to work its way down the list until the last cell and then do the same but without the comma. I can do the loop, its just the other bit.

Posted by Robb on October 03, 2001 3:11 AM


If it's just a matter of removing the comma on the second run, try something like

Sub FmatCs()
With Worksheets("Sheet1")
For Each c In .Range("A1:A900").Cells
Dim myC
myC = c
If myC <> "" Then c.Formula = ":""" & myC & ""","
Next c
For Each c In .Range("A1:A900").Cells
myC = c
If myC <> "" Then c.Formula = Left(myC, Len(myC) - 1)
Next c
End With
End Sub

Of course, if you don't want it all at the same time, just separate the
For each statements.

Any help?