macro to edit cell contents

[Guest]

I need to edit a cell's contents by insert at ^ and the beginning and end of
what ever is in the cell, then move down to the next cell to be ready for the
next one.

Macro recorder can't seem to handle this.

Thanks.

 

[Guest]

If I understand correctly, you'd like to insert a carat symbol at the
beginning and end of the contents of a cell (e.g., "harry" becomes "^harry^")
and then keep moving down the list of items and editing each one in the same
way until you run out of items. If that's what you want to do, then perhaps
this helps (you'll need to set focus on the first item in the list that you
want to edit and I'm assuming that you have no blanks in your list):

Sub EditCells()
Dim i As Integer
Do Until ActiveCell.Value = ""
ActiveCell.Value = "^" & ActiveCell.Value & "^"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Gary Keramidas]

here's one way, assuming your values start in a1 and are in 1 column

Sub test()
Dim lastrow As Long, cell As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("A1:A" & lastrow)
cell.Value = "^" & cell.Value & "^"
Next
End Sub

 

[Guest]

Sorry, you can delete the declaration of i (dim i as integer) in the code
below).

 

[Guest]

Thanks everyone, worked great.
--
Alan

here's one way, assuming your values start in a1 and are in 1 column

Sub test()
Dim lastrow As Long, cell As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("A1:A" & lastrow)
cell.Value = "^" & cell.Value & "^"
Next
End Sub