Tuesday, July 7, 2020

VBA Replace Text In Range in Excel


Sub ReplaceTextInRange(sht As Worksheet, rngSearch As Range, strFindText As String,  strReplaceText As String)

    Dim cellFound As Range
    Dim firstAddress As String

    ActiveWorkbook.Sheets(sht.Name).Activate
 
    With rngSearch
        Set cellFound = .Find(strFindText, LookIn:=xlValues)
        If Not cellFound Is Nothing Then
            firstAddress = cellFound.Address
            Do
                cellFound.Value = VBA.Replace(cellFound.Value, strFindText, strReplaceText)
                Set cellFound = .FindNext(cellFound)
''            Loop While cellFound.Address <> firstAddress
            Loop Until cellFound Is Nothing
        End If
    End With

End Sub

No comments:

Post a Comment

Hot Topics