[VBA] Preserve format replace function

MrD101

New Member
Joined
Sep 2, 2021
Messages
10
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This code allows me to copy a formatted string in a cell and put it in a new cell preserving the format (including bold and colored text).
For example: "The bull was seeing red."

VBA Code:
Application.CutCopyMode = False
ActiveCell.Copy
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste

If I try to replace something I lose the string formatting (bold and colored text).

VBA Code:
Range("A2").Value = Replace(Range("A2").Value, Find:="was", Replace:="is")

Is there a way to do a replace while preserving the formatting that will work for large strings (larger than 255 characters)? I'd also be open to seeing a better copy solution if anyone has one.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

rlv01

Well-known Member
Joined
May 16, 2017
Messages
2,025
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The copy part can be simplified:

VBA Code:
  ActiveCell.Copy Worksheets("Sheet1").Range("A2")

The other part is harder. The replace function will always erase that kind of partial string formatting within a cell and there is no general excel or vba function that will preserve that kind of partial cell formatting for any cell you throw at it. At least that I know of. If you are lucky maybe someone will come along and prove me wrong and we'll both learn something. If you have a very narrow and well defined formatting requirement, such as "the last word must always use a red font color" then a vba solution becomes possible.
 
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,126
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Don't use Replace, rather, use the Range object's Characters property...
VBA Code:
Range("A2").Characters(InStr(Range("A2").Value,"was"),3).Text = "is"
Note: The 3 is the length of the text "was".
 
Last edited:
Upvote 0

MrD101

New Member
Joined
Sep 2, 2021
Messages
10
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Don't use Replace, rather, use the Range object's Characters property...
VBA Code:
Range("A2").Characters(InStr(Range("A2").Value,"was"),3).Text = "is"
Note: The 3 is the length of the text "was".
Can you use this method to change a character to a new line feed?

For example:

Range("A2").Characters(InStr(Range("A2").Value, "~"), 1).Text = Chr(10)

I can't get something like that to work, but that is my end goal at the moment.
 
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,126
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
That line of code worked fine for me... don't forget that to see the effect of that code line you have to increase the rows height and possibly set the cell's WrapText property to True (although I think that may happen automatically).
 
Upvote 0

MrD101

New Member
Joined
Sep 2, 2021
Messages
10
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
That line of code worked fine for me... don't forget that to see the effect of that code line you have to increase the rows height and possibly set the cell's WrapText property to True (although I think that may happen automatically).
Would this only work for the first instance of the "~"? I can get it to work for a single sentence but if I have multiple sentences I have seen it work for the first instance and for larger sets of strings it doesn't appear to do anything.

For example, if I have:
The bull was seeing red.~This is a test too. The bull was seeing blue.~This is a test too.

It only replaces the first instance. If I paste that string about 16 times it doesn't appear to do anything.

Thanks again for all the assistance!
 
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,126
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Yes, as written, only for the first occurrence. You did not say earlier that you could have more than one occurrence within a cell. To cover that possibility will require a loop. Something like this...
VBA Code:
Do While InStr(Range("A2"),Value, "~")
  Range("A2").Characters(InStr(Range("A2").Value, "~"), 1).Text = Chr(10)
Loop
 
Upvote 0

MrD101

New Member
Joined
Sep 2, 2021
Messages
10
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Yes, as written, only for the first occurrence. You did not say earlier that you could have more than one occurrence within a cell. To cover that possibility will require a loop. Something like this...
VBA Code:
Do While InStr(Range("A2"),Value, "~")
  Range("A2").Characters(InStr(Range("A2").Value, "~"), 1).Text = Chr(10)
Loop
Ok, thank you. If run that loop it never ends and kills the program, but I realize that was just an example so I can work on that! Thanks so much.

One last question from the beginning.

If I use the simplified code from above:
VBA Code:
ActiveCell.Copy Worksheets("Sheet1").Range("A2")

That works for a cell. Is it possible to use the copy function into a textbox?
 
Upvote 0

Forum statistics

Threads
1,186,707
Messages
5,959,282
Members
438,410
Latest member
Avskedsfest

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top