Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - macro messes up the character formating
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" he 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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - macro messes up the character formating
On Fri, 5 Feb 2010 02:42:55 -0800 (PST), Ivaylo
wrote: 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" he 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - macro messes up the character formating
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? On Feb 5, 1:16*pm, Ron Rosenfeld wrote: On Fri, 5 Feb 2010 02:42:55 -0800 (PST), Ivaylo wrote: 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" hehttp://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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - macro messes up the character formating
On Fri, 5 Feb 2010 05:11:44 -0800 (PST), Ivaylo
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort messes up pictures | Excel Discussion (Misc queries) | |||
Search and Replace a Spacific Character with Conditional Formating | Excel Programming | |||
Date Formating and building character strings | Excel Discussion (Misc queries) | |||
auto-filter messes up the formulas | Excel Discussion (Misc queries) | |||
Character formating for User defined function problem | Excel Programming |