Search code examples
excelvbafontsinsertcharacter

Insert string into cell with only integers while keeping fonts- VBA

I am trying to append text into an excel cell with VBA. Currently I am using :

Range("A1").Characters(Len(Range("A1")) + 1).Insert ("text")

This works fine for cells with text, but if the cell contains only numbers (integers) it throws an error:

Run-time error '1004' Insert method of Characters class failed

Parts of the text in the cell are in different fonts or bolded and I need to keep them that way.

I have also tried to change the NumberFormat the cell to general, but it does not help:

Range("A1").NumberFormat = "@"

If someone knows a way to append text to a cell containing only integers with different fonts please answer.

Solution

  • Unlike Strings, Cells that contain actual Numbers (but not strings that look like numbers) cannot have format applied individual characters.

    Therefore split your processing based on data type1

    Dim cell as Range
Dim AppendText as String
AppendText = "text"
Set cell = Range("A1")
If TypeName(cell.Value2) <> "String" Then
    cell.Value2 = cell.Value2 & AppendText
Else
    cell.Characters(Len(cell.Value2) + 1).Insert AppendText
End If

    After this has run, cells thar were numeric will now be strings, and can have individual characters formmated.

    Notes:

    1. You might want to consider other data types too: boolean, date, ...