Excel, VBA: characters insert method does not insert

%3CLINGO-SUB%20id%3D%22lingo-sub-1584575%22%20slang%3D%22en-US%22%3EExcel%2C%20VBA%3A%20characters%20insert%20method%20does%20not%20insert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584575%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I'm%20using%20excel%20and%20hit%20a%20rock.%20The%20problem%20is%20that%20a%20cell%20that%20I%20need%2Fwant%20to%20change%20it's%20contents%2C%20has%20some%20mixed%20formatting%20(colours%2C%20bold%20and%20italic).%20Using%20the%20replace%20method%2C%20all%20formatting%20disappears.%20Next%2C%20I've%20tried%20Characters%20Delete%26nbsp%3B%20and%20Insert%20methods.%20These%20do%20either%20nothing%20(Delete)%20or%20replace%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EI%20have%20a%20cell%20A1%20with%20contents%20%22aap%20noot%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3E%3CSTRONG%3Emies%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FFONT%3E%22%20where%20%22mies%22%20is%20red%2C%20bold%20and%20italic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20run%20this%20VBA%20script%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20test_Click()%3CBR%20%2F%3EDim%20txt%20As%20String%3CBR%20%2F%3Etxt%20%3D%20%22vuur%20boom%20roos%22%20%26amp%3B%20vbNewLine%3CBR%20%2F%3ESheet1.Cells(1%2C%201).Characters(5).Insert%20txt%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22mies%22%20gets%20replaced%20with%20%22vuur%20boom%20roos%22%20followed%20by%20a%20NewLine.%20And%20I%20expected%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eaap%20noot%20vuur%20boom%20roos%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3E%3CSTRONG%3Emies%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20%22mies%22%20stays%20red%2C%20bold%20and%20italic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attachments.%20excel-vba-orig.png%20is%20the%20starting%20point%2C%20excel-vba-changed.png%20is%20what%20I%20get%20and%20excel-vba-expected.png%20is%20what%20I%20expected.%20And%20test.xlsm%20is%20a%20simple%20test%20excel%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%2C%20I'm%20Dutch.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20solution%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1584575%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hello, I'm using excel and hit a rock. The problem is that a cell that I need/want to change it's contents, has some mixed formatting (colours, bold and italic). Using the replace method, all formatting disappears. Next, I've tried Characters Delete  and Insert methods. These do either nothing (Delete) or replace text.

 

Example:

I have a cell A1 with contents "aap noot mies" where "mies" is red, bold and italic.

 

When I run this VBA script:

 

Sub test_Click()
Dim txt As String
txt = "vuur boom roos" & vbNewLine
Sheet1.Cells(1, 1).Characters(5).Insert txt
End Sub

 

"mies" gets replaced with "vuur boom roos" followed by a NewLine. And I expected:

 

aap noot vuur boom roos
mies

 

And "mies" stays red, bold and italic.

 

See attachments. excel-vba-orig.png is the starting point, excel-vba-changed.png is what I get and excel-vba-expected.png is what I expected. And test.xlsm is a simple test excel file.

 

Note, I'm Dutch.

 

Is there any solution?

0 Replies