Replace Last occurrence of a character

Question

I have a spreadsheet with lots of paths to files in them. I use the following formula to replace the last occurrence of a "\" in each cell of a column. How can I change this to a macro so I don't have to paste in and drag down each column.

=SUBSTITUTE(K2,"\","?",LEN(K2)-LEN(SUBSTITUTE(K2,"\","")))

I tried to record a macro and this works but only on one cell and only if the active cell is in the O Column

Sub Macro4()
ActiveCell.FormulaR1C1 = _
    "=SUBSTITUTE(RC[-4],""\"",""?"",LEN(RC[-4])-LEN(SUBSTITUTE(RC[-4],""\"","""")))"
Range("O2").Select
End Sub

I need to have this put the value in O column starting at O2 for each non empty K starting with K2 regardless of what the active cell is.

Answer 1

Here is an alternative approach:

Sub LastSlash()
    Dim N As Long, i As Long, rng As Range, r As Range
    Dim rc As Long, L As Long, j As Long

    rc = Rows.Count
    Set rng = Intersect(ActiveSheet.UsedRange, Range("K2:K" & rc))
    N = Cells(rc, "K").End(xlUp).Row

    For Each r In rng
        s = r.Value
        L = Len(s)
        If InStr(1, s, "\") > 0 Then
            For j = L To 1 Step -1
                If Mid(s, j, 1) = "\" Then
                    Mid(s, j, 1) = "?"
                    Exit For
                End If
            Next j
        End If
        r.Offset(0, 4).Value = s
    Next r
End Sub