CODEWITHSUNDEEP
excelvba
roljdj666
roljdj666

Reputation: 15

Adding suffix to thousands of cells VBA

What is an efficient way of adding a suffix (in this case simply "ID") to thousands of cells (500,000 cells) using VBA?

The simple and usual 'for loop' in VBA takes far too long. (e.g. For Each Cell In Selection.Cells etc. takes tens of minutes for the number of cells).

I've written this below (based off internet searches), and works on Excel 365, but not on Excel 2016. Instead, in 2016 it takes the top left cell and just pastes that across all selected cells. Any thoughts on how to rectify this? Many thanks.

Sub AddTextToEndOfCellValue()
Dim Suffix As String
Suffix = "ID"
Selection = Evaluate(Replace("REPLACE(@,LEN(@)+1,0,""" & Suffix & """)", "@", Selection.Address))
Selection.Replace Suffix, "", xlWhole, , , , False, False
End Sub

input and expected output sample

Upvotes: 0

Views: 305

Answers (2)

norie
norie

Reputation: 9857

I can only test this on Excel 365 but I'm sure I've successfully used similar code in earlier versions.

This will add the suffix to all the selected cells.

Sub AddTextToEndOfCellValue()
Dim Suffix As String
    Suffix = "ID"
    With Selection
        .Value = Evaluate(.Address & "&""" & Suffix & """")
    End With
End Sub

This will add the suffix to the selected cells that have a value in them, as per the before/after image you posted.

Sub AddTextToEndOfCellValue()
Dim Suffix As String
    Suffix = "ID"
    With Selection
        .Value = Evaluate("IF(" & .Address & "="""", """"," & .Address & "&""" & Suffix & """)")
    End With
End Sub

Upvotes: 1

Naresh
Naresh

Reputation: 3034

Assuming the range is "A2:D5"

Sub AddSuffixinSelectedRange()
Dim rng As Range, newVal
Set rng = Selection
newVal = Evaluate("A2:D5&" & """_ID""")
rng = newVal
End Sub

This will replace values in place.. So better try it on a sample/ copy first.

Upvotes: 0

Related Questions