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?

I would not use macros for this but rather create a helper column and
concatenate the text in the target cell with the tilde.

Example: =concatenate(A1,"~")

Run the formula down as you need it, then copy the helper column and paste
its values over column A then delete the helper column.

Dave
--
Brevity is the soul of wit.


"tevid" wrote:

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?

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

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"tevid" wrote in message
ups.com...
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?

Here's some modified code that Biff posted a while back.........it works on
the selected cells.........

Sub AddToCellValue()
Dim cell As Range
For Each cell In Selection
If cell.Value < "" Then
cell.Value = cell.Value & "~"
Else: cell.Value = cell.Value
End If
Next cell
End Sub


Vaya con Dios,
Chuck, CABGx3




"tevid" wrote:

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?

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.

Select
tevid wrote:

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?

--

Dave Peterson

Nice macro Dave........I know it is a super-simple thing for ou, but it's
well laid out for easy readability. Shows us novices a clear way to step
through a range qualifying certain conditions....not wrought with difficult
to understand sequences........a good template to use for other
things........a keeper!

Thanks for posting it.

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote:

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.

Select
tevid wrote:

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?

--

Dave Peterson

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

CLR wrote:

Nice macro Dave........I know it is a super-simple thing for ou, but it's
well laid out for easy readability. Shows us novices a clear way to step
through a range qualifying certain conditions....not wrought with difficult
to understand sequences........a good template to use for other
things........a keeper!

Thanks for posting it.

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote:

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.

Select
tevid wrote:

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?

--

Dave Peterson


--

Dave Peterson