Add string to active merged cells only once - Excel VBA macro

Question

I have a column of various horizontally merged cells (3) with text and I want to add a string before every selected one of them but only once (to a merged cells as one).

I have this code but it adds the string three times - counts every single cell in a merged one:

Sub Add2Formula()
' Add text

For Each c In Selection
c.Activate
ActiveCell.FormulaR1C1 = "Text - " & ActiveCell.Formula
Next c

End Sub

score 1 · Accepted Answer · answered Mar 26 '21 at 10:51

Don't select or activate anything in VBA (read How to avoid using Select in Excel VBA). Use c directly and your problem will be gone. Writing into a cell that is part of a merged cell and not the first cell of the merged area will simply be ignored.

Sub Add2Formula()
    Dim c As Range
    For Each c In Selection
        c.Value = "Text - " & c.Value
    Next c
End Sub

Add string to active merged cells only once - Excel VBA macro

1 Answers1