Adding a Symbol to all columns in a row

[tevid]

Say I have 5000 cells in a row that i need to add a symbol into, in
this case ~. I have come up with a macro that will let me do it for
one cell at a time, but i need to do it for all of them at once.

Here is what i have so far:

Sub Tester8()
ActiveCell.FormulaR1C1 = Chr(126)
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Regular"
.Size = 9
End With
End Sub

say i have c52 selected, when i run the macro it inserts the ~ as
expected but it also removes all text that was previously in the box.

Any ideas how i go about adding the symbol to all rows at once while
still preserving the text?

 

[Earl Kiosterud]

tevid,

You don't say what "add" means. But try this code and see if you can modify
it to do what you want. Keep a copy of the original column, as macro antics
aren't undoable.

Sub AddChar()
Dim Column As Integer, RowStart As Long, RowEnd As Long
Dim i As Integer

Dim Char As String
Char = "~" ' or Char = Chr(126)
Column = 1 ' column
RowStart = 2 ' starting row
RowEnd = 10 ' ending row

For i = RowStart To RowEnd
Cells(i, Column) = Char & Cells(i, Column)
Next i
End Sub

 

[Dave Peterson]

When I changed my font to Symbol, the character looked pretty much the same.

But if you really want that first character to be Symbol, regular and 9.

Option Explicit
Sub Tester8a()
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection

For Each myCell In myRng.Cells
If myCell.HasFormula Then
'do nothing
Else
If myCell.Value = "" Then
'do nothing
Else
myCell.Value = Chr(126) & myCell.Value
With myCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Regular"
.Size = 9
End With
End If
End If
Next myCell
End Sub


Select your range first.

 

[Dave Peterson]

But I still couldn't tell much difference between chr(126) in Arial and Symbol!