Macro that replaces last word in a cell

user_18310956 0
Viewed 38

I've been trying to come up with a macro that runs through a column and replaces the last word in a cell.

Dim LR As Long, i As Long
    Application.ScreenUpdating = False
    LR = Range("A" & Rows.Count).End(xlUp).row
        For i = 1 To LR
            With Range("A" & i)
                If Right(.Value, 4) = "word" Then .Value = Right(.Value, Len(.Value) + 10) & " different word"
            End With
        Next i

I have this code that tacks the replacement onto the end, but I don't understand it well enough to get it to replace the original.

Any input appreciated.

vba excel
1 Answers

I like to use RegEx .Replace for situations like this. The magic here is that the dollar sign $ means "end of the line". So where I have RegEx.Pattern = "word$" that means it will only match word if it's the last thing in that line of text.

Dim LR As Long
Dim i As Long
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")

RegEx.Global = True
RegEx.Pattern = "word$"

LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .Value = RegEx.Replace(.Value, " different word")
    End With
Next i
user_81738700
Related