Help - macro messes up the character formating

[Ivaylo]

Greetings,

I need help with a macro.

I use this macro in Excel 2007:

Sub ReplaceTags2()
For Each c In ActiveCell.CurrentRegion.Cells
c.Value = Application.WorksheetFunction.Substitute(c, "####", Chr(10)
& Chr(10))
Next
End Sub

My data contains the markers ####, which I want to replace with
linebreaks.

The macro does its work but messes up the formatting in the cells. I
uploaded the file "Book1.xlsm" here: http://www.box.net/shared/8rdsg533qp

The file contains a sample of the data + the macro itself.

Can anyone help me modify the macro to do the replacement without
changing anything to the cell formatting?
Thanks in advance!

 

[Ron Rosenfeld]

Greetings,

I need help with a macro.

I use this macro in Excel 2007:

Sub ReplaceTags2()
For Each c In ActiveCell.CurrentRegion.Cells
c.Value = Application.WorksheetFunction.Substitute(c, "####", Chr(10)
& Chr(10))
Next
End Sub

My data contains the markers ####, which I want to replace with
linebreaks.

The macro does its work but messes up the formatting in the cells. I
uploaded the file "Book1.xlsm" here: http://www.box.net/shared/8rdsg533qp

The file contains a sample of the data + the macro itself.

Can anyone help me modify the macro to do the replacement without
changing anything to the cell formatting?
Thanks in advance!

If you don't get a response, post more data here in text form. Many of us will
not upload unsolicited files from the web for fear of infection.
--ron

 

[Ivaylo]

Ok, Ron.

It's a dictionary database which need to be manipulated in Excel

Column A contains words. Column B contains a longer text which
contains formatting like bold, italic, underline, and some words in
color.

When I run the macro, it replaces the markers #### with a linebreak
[Chr(10)]. However, the formatting in column B is messed up - no
italic, no bold, very often the whole text is in some of the color
that some of the words had before running the macro - e.g. red, blue,
etc.

I'd like to replace my markup symbol (####) with a linebreak and
preserving the formatting of the cell contents as is.

Any idea how to modify the macro to do this?

 

[Ron Rosenfeld]

Ok, Ron.

It's a dictionary database which need to be manipulated in Excel

Column A contains words. Column B contains a longer text which
contains formatting like bold, italic, underline, and some words in
color.

When I run the macro, it replaces the markers #### with a linebreak
[Chr(10)]. However, the formatting in column B is messed up - no
italic, no bold, very often the whole text is in some of the color
that some of the words had before running the macro - e.g. red, blue,
etc.

I'd like to replace my markup symbol (####) with a linebreak and
preserving the formatting of the cell contents as is.

Any idea how to modify the macro to do this?

Given the information you've provided:

Differential formatting of characters within a cell in Excel can only be done
if the contents is a text string. So I will assume that the contents of Column
B is a text string, and not the results of a formula, or a picture, or
something else.

That being the case, because of the way Excel does this process, I don't
believe you can merely do the substitution. I believe you will need to do the
substitution, and then reformat the characters as they were before.

So you will probably need to modify your macro to look at each character in
sequence, obtain the font characteristics that might be different, and then
write the string back into the cell with the substitution made and the
appropriate formatting.
--ron