I'm not very familiar with VBA, but am in need of a macro that would replace ### to = for columns B, F, and J without affecting rows 1 to 15.

I found the below code as my starting point, but can't seem to adjust it to my situation, or if this would even work for my situation...

this code was replacing everything in B that was not empty to Title starting at B2

Sub replace_text
Dim lrow As Long
Dim rng As Range

    lrow = Cells(Rows.Count, "B").End(xlUp).Row 'Detect last data entry row in Column B.
    
    For Each rng In Range("B2:B" & lrow)
        If rng <> "" Then
            rng = "Title"
        End If
    Next rng

End Sub

essentially, my goal is to replace every occurrences of text strings that start with ### with = without affecting the remainder of the strings.

everything in column B, F, and J starting at row 16 would start with ### but do not have the same text after the ###.

other columns would also have ### at the start but would need to remain intact

CodePudding user response：

Worksheet.Evaluate: Replace Text

Sub ReplaceText()

    Const FIRST_ROW As Long = 16
    Const COLUMNS_RANGE As String = "B:B,F:F,J:J"
    Const BEGINS_WITH_STRING As String = "###"
    Const REPLACE_STRING  As String = "="
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range
    
    With ws.UsedRange
        Set rg = .Resize(.Rows.Count   .Row - FIRST_ROW).Offset(FIRST_ROW - 1)
    End With
    
    Dim arg As Range, aAddress As String, Formula As String
       
    For Each arg In Intersect(rg, ws.Range(COLUMNS_RANGE)).Areas
        aAddress = arg.Address
        Formula = "IF(LEFT(" & aAddress & "," & Len(BEGINS_WITH_STRING) _
            & ")=""" & BEGINS_WITH_STRING & """," & "SUBSTITUTE(" _
            & aAddress & ",""" & BEGINS_WITH_STRING & """,""" _
            & REPLACE_STRING & """,1)," & aAddress & ")"
        'Debug.Print Formula
        arg.Value = ws.Evaluate(Formula)
    Next arg
    MsgBox "Text replaced.", vbInformation

End Sub