Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and Replace With Formatted Text
How can I use VBA code to replace text in one cell with formatted text from another cell? Here is an example. Cell a1 contains text that may include italicized or bolded characters. The content and format of cell a1 is controlled by the user. Cell b1 contains text to search for and does not contain character formatting. Cell c1 contains the target text, some of which will be replaced. It does not contain character formatting. How can I search c1 for the text in b1 and replace it with the contents of a1 while keeping any formatted characters in a1? I have no trouble doing the search and replace, but cannot figure out how to preserve any formatted text. Any suggestions? Taxi -- Taxi Houston ------------------------------------------------------------------------ Taxi Houston's Profile: http://www.excelforum.com/member.php...o&userid=14818 View this thread: http://www.excelforum.com/showthread...hreadid=264496 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and Replace With Formatted Text
You need to use the Characters property of the Range object. IMO, this isn't
pretty and will probably be quite slow. But here goes... Sub CopyCharacterFormats() Dim Src As Range Dim Dest As Range Dim i As Long Dim j As Long Set Src = Worksheets("Sheet2").Range("A1") Set Dest = Worksheets("Sheet2").Range("C1") Dest.Value = "xxx" & Src.Value & "yyy" j = 4 'must be set to point where you inserted the text For i = 1 To Len(Src.Value) Dest.Characters(j, 1).Font.Bold = Src.Characters(i, 1).Font.Bold Dest.Characters(j, 1).Font.Italic = Src.Characters(i, 1).Font.Italic j = j + 1 Next i End Sub On Tue, 28 Sep 2004 22:31:47 -0500, Taxi Houston wrote: How can I use VBA code to replace text in one cell with formatted text from another cell? Here is an example. Cell a1 contains text that may include italicized or bolded characters. The content and format of cell a1 is controlled by the user. Cell b1 contains text to search for and does not contain character formatting. Cell c1 contains the target text, some of which will be replaced. It does not contain character formatting. How can I search c1 for the text in b1 and replace it with the contents of a1 while keeping any formatted characters in a1? I have no trouble doing the search and replace, but cannot figure out how to preserve any formatted text. Any suggestions? Taxi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REPLACE PART OF CELL WITH FORMATTED TEXT | Excel Worksheet Functions | |||
How do I search and replace text in a textbox in Excel? | Excel Discussion (Misc queries) | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions | |||
Search and Replace | Excel Programming | |||
Search and Replace text strings within Macros | Excel Programming |