I need to add and remove characters from the end of cells in an
existing worksheet. Some of the existing characters in the cells have
character formats like bold or italic.
The problem is that I cannot find a way to preserve the existing
character formatting in the cells. The following example will lose all
character formats in a cell.
ActiveCell.Value = ActiveCell.Value & "@"
It is easy to do by hand but it seems impossible through VBA.
Is there an easy way of preserving the existing character formats?

this doesn't change formatting for me, unless i'm missing something. if it's
bold, it stays bold, if it's bold and italic, it stays bold and italic.
ActiveCell.Value = ActiveCell.Value & "@"


--


Gary


"Kobus" wrote in message
ups.com...
I need to add and remove characters from the end of cells in an
existing worksheet. Some of the existing characters in the cells have
character formats like bold or italic.
The problem is that I cannot find a way to preserve the existing
character formatting in the cells. The following example will lose all
character formats in a cell.
ActiveCell.Value = ActiveCell.Value & "@"
It is easy to do by hand but it seems impossible through VBA.
Is there an easy way of preserving the existing character formats?

Funny. Like Gary said, works for me too.
Try a work around by copying the cell and pasting the formats:

ActiveCell.Copy
ActiveCell.Value = ActiveCell.Value & "@"
ActiveCell.PasteSpecial xlPasteFormats

Sharad

"Kobus" wrote in message
ups.com...
I need to add and remove characters from the end of cells in an
existing worksheet. Some of the existing characters in the cells have
character formats like bold or italic.
The problem is that I cannot find a way to preserve the existing
character formatting in the cells. The following example will lose all
character formats in a cell.
ActiveCell.Value = ActiveCell.Value & "@"
It is easy to do by hand but it seems impossible through VBA.
Is there an easy way of preserving the existing character formats?

the OP is talking about Rich Text formatting, where individual characters
within the cell have different formatting. Some individual characters may
be bold, but not all characters are bold for example. This certainly
exhibits the behavior the OP described.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this doesn't change formatting for me, unless i'm missing something. if
it's bold, it stays bold, if it's bold and italic, it stays bold and
italic.
ActiveCell.Value = ActiveCell.Value & "@"


--


Gary


"Kobus" wrote in message
ups.com...
I need to add and remove characters from the end of cells in an
existing worksheet. Some of the existing characters in the cells have
character formats like bold or italic.
The problem is that I cannot find a way to preserve the existing
character formatting in the cells. The following example will lose all
character formats in a cell.
ActiveCell.Value = ActiveCell.Value & "@"
It is easy to do by hand but it seems impossible through VBA.
Is there an easy way of preserving the existing character formats?

Assuming Rich Text Formatting as you describe:

This example maintains the bold and Italic character formatting. You would
need to add an array for each formatting attribute you want to maintain :

Sub AppendData()
Dim rng As Range, l As Long
Dim i As Long, arrBold() As Variant
Dim arrItalic As Variant
Set rng = ActiveCell
l = Len(rng)
ReDim arrBold(1 To l)
ReDim arrItalic(1 To l)
' Capture the formatting
For i = 1 To l
arrBold(i) = rng.Characters(i, 1).Font.Bold
arrItalic(i) = rng.Characters(i, 1).Font.Italic
Next
rng.Value = rng.Value & "@"
For i = 1 To l
rng.Characters(i, 1).Font.Bold = arrBold(i)
rng.Characters(i, 1).Font.Italic = arrItalic(i)
Next
End Sub

--
Regards,
Tom Ogilvy



"Kobus" wrote in message
ups.com...
I need to add and remove characters from the end of cells in an
existing worksheet. Some of the existing characters in the cells have
character formats like bold or italic.
The problem is that I cannot find a way to preserve the existing
character formatting in the cells. The following example will lose all
character formats in a cell.
ActiveCell.Value = ActiveCell.Value & "@"
It is easy to do by hand but it seems impossible through VBA.
Is there an easy way of preserving the existing character formats?

Right Tom!, That explains it.

"Tom Ogilvy" wrote in message
...
the OP is talking about Rich Text formatting, where individual characters
within the cell have different formatting. Some individual characters may
be bold, but not all characters are bold for example. This certainly
exhibits the behavior the OP described.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this doesn't change formatting for me, unless i'm missing something. if
it's bold, it stays bold, if it's bold and italic, it stays bold and
italic.
ActiveCell.Value = ActiveCell.Value & "@"


--


Gary


"Kobus" wrote in message
ups.com...
I need to add and remove characters from the end of cells in an
existing worksheet. Some of the existing characters in the cells have
character formats like bold or italic.
The problem is that I cannot find a way to preserve the existing
character formatting in the cells. The following example will lose all
character formats in a cell.
ActiveCell.Value = ActiveCell.Value & "@"
It is easy to do by hand but it seems impossible through VBA.
Is there an easy way of preserving the existing character formats?

On Mar 17, 7:47 pm, "Sharad" wrote:
Right Tom!, That explains it.

"Tom Ogilvy" wrote in message

...



the OP is talking about Rich Text formatting, where individual characters
within the cell have different formatting. Some individual characters may
be bold, but not all characters are bold for example. This certainly
exhibits the behavior the OP described.

--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this doesn't change formatting for me, unless i'm missing something. if
it's bold, it stays bold, if it's bold and italic, it stays bold and
italic.
ActiveCell.Value = ActiveCell.Value & "@"

--

Gary

"Kobus" wrote in message
roups.com...
I need to add and remove characters from the end of cells in an
existing worksheet. Some of the existing characters in the cells have
character formats like bold or italic.
The problem is that I cannot find a way to preserve the existing
character formatting in the cells. The following example will lose all
character formats in a cell.
ActiveCell.Value = ActiveCell.Value & "@"
It is easy to do by hand but it seems impossible through VBA.
Is there an easy way of preserving the existing character formats?- Hide quoted text -

- Show quoted text -

Thank you. I think Tom got a solution. Lets hope that the only
formatting is bold and italic. I presume it would be difficult to
check for all formatting (such as colours, underline subscript,
superscipt, caps, etc.).
I was hoping that VBA would have a more elegant solution.