Search and Replace With Formatted Text
 

A little tricky, but with a little finagling it works great. Muc
obliged. I'm not sure what the <<Dest.Value = "xxx" & Src.Value
"yyy" line is all about. I had to remove it to get it to work.

Taxi


Myrna Larson
Guest

Search and Replace With Formatted Text
You need to use the Characters property of the Range object. IMO, thi
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 formatte
text
from another cell?

Here is an example.

Cell a1 contains text that may include italicized or bolde
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 th
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 Housto
-----------------------------------------------------------------------
Taxi Houston's Profile: http://www.excelforum.com/member.php...fo&userid=1481
View this thread: http://www.excelforum.com/showthread.php?threadid=26449

Search and Replace With Formatted Text
 

I used that line when testing the code on a blank worksheet and forgot to
take it out before posting. You did the right thing.

"Taxi Houston" wrote in message
...

A little tricky, but with a little finagling it works great. Much
obliged. I'm not sure what the <<Dest.Value = "xxx" & Src.Value &
"yyy" line is all about. I had to remove it to get it to work.

Taxi


Myrna Larson
Guest

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 :)


--
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