[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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
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
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
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
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
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
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
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".
This type of approach (nor Insert method) does not work for me when cell string length is longer (presumably longer than 255 characters), while it works (they both work) for cells with shorter strings. Note that format adjustment works also on long strings, but I did not find a way to get the text replacements to work...
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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