question

KenKrugh-6537 avatar image
0 Votes"
KenKrugh-6537 asked KenKrugh-6537 commented

Characters.Insert in Excel

I have a script that turns a single line break within an Excel cell into two. The code is pretty simple:
Set Cel = ActiveCell
For c = Cel.Characters.Count To 1 Step -1
If Cel.Characters(c, 1).Text = vbLf Then
Cel.Characters(c, 1).Insert (vbLf & vbLf)
End If
Next c

It works in most instances but there are places where the .Insert command doesn't have any affect. I know there are VbLf there and they're being "found" but the Insert command doesn't always add the double VbLf.

Has anyone seen this not work properly?

Thanks,
Ken

office-vba-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Edit: It has to do with the length of the cell. As soon as it reaches 255 characters the .Insert command no longer works.

Anyway around this?

Thanks again!

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered KenKrugh-6537 commented

Consider this approach:

 Dim r As Range
 Set r = ActiveCell
    
 r.Value = Replace(r.Value, vbLf, vbLf & vbLf)

However, the inner formatting of characters will be lost.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the reply, Viorel. Sorry, I should have indicated that I'd already tried that.

Unfortunately, just like the regular replace in Excel, this sets the font style of ALL the text in the cell to be whatever is at the start of the cell, and many of the cells have regular, italic and bold used within the same cell.

As a side note, yes, we did suggest they'd be better done in Word, but they need Excel.

Thanks again!

0 Votes 0 ·