Find and replace all occurences of a string in a range

Question

I want to basically simulate the replace all feature in Excel inside VBA and replace the string 03/01/2018 (which exists 10 times in this workbook) with 01/03/2017 I already figured out how to do that for one single occurrence but not to all occurrences inside a range.

Is there something like findnext for the replace method?

Sub findandreplacedate()
    Workbooks("01 .xlsx").Sheets(1).usedrange.Replace What:="*03/01/2018*", _
    Replacement:="01/03/2017", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
end sub

Answer 1

Multiple research and replacement of strings in Excel

This is a VBA code for multiple research and replacement of strings in Excel selected sheet cells

My goal was to find and replace for bad character encoding in active sheet :

• é -> (replaced by) é
• è -> è
• ’ -> '
• î -> î
• ê -> ê
• Ã -> à

Common Functions required found in https://www.rondebruin.nl/win/s3/win002.htm

Sub Multi_FindReplace() adapted from https://www.mrexcel.com/board/threads/find-and-replace-multiple-values.1230258/

' Common Functions required for all routines

' Find the last row with data in sheet
Function LastRow(Sh As Worksheet)
    On Error Resume Next
    LastRow = Sh.Cells.Find(What:="*", _
                            After:=Sh.Range("A1"), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

' Find the last col with data in sheet
Function LastCol(Sh As Worksheet)
    On Error Resume Next
    LastCol = Sh.Cells.Find(What:="*", _
                            After:=Sh.Range("A1"), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

' Find and replace for bad character encoding in active sheet
' é  -> é
' è -> è
' ’ -> '
' î -> î
' ê -> ê
' Ã -> à

Sub Multi_FindReplace()

    Dim Sh As Worksheet
    Dim LastR, LastC As Long
    Dim Range As Range
    Dim FindTips As Variant
    Dim RplcTips As Variant
    Dim y As Long
    
    ' Search
    FindTips = Array("é", "è", "’", "î", "ê", "Ã")
    ' Replacement
    RplcTips = Array("é", "è", "'", "î", "ê", "à")
    
    ' Select active sheet
    ActiveSheet.Select
    Set Sh = ActiveSheet
    
    ' Find the last row with data
    LastR = LastRow(Sh)
    ' MsgBox LastR
    
    ' Find the last col with data
    LastC = LastCol(Sh)
    ' MsgBox LastC
    
    ' Select Cells Range
    Set Range = ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(LastR, LastC))
    
    With Range
        For y = LBound(FindTips) To UBound(FindTips)
            Range.Replace What:=FindTips(y), Replacement:=RplcTips(y), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
        Next y
    End With
    

End Sub

Answer 2

Yes there is FindNext Range.FindNext Method (Excel)

To find all instances of text in a range you can use FindNext with your Find the following example shows how to use FindNext.

Option Explicit
Public Sub Example()
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets(1).UsedRange _
                          .Find("03/01/2018", LookIn:=xlValues)

    If rng Is Nothing Then
        Debug.Print "Not Found"
        Exit Sub
    End If

    Dim firstAdd As String
    firstAdd = rng.Address

    Do ' Print address
        DoEvents
        Debug.Print rng.Address
        ' Find next item
        Set rng = ThisWorkbook.Worksheets(1).UsedRange.FindNext(rng)
    Loop Until rng Is Nothing Or firstAdd = rng.Address
End Sub

---

Other info

_{DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.}

---

_{Debug.Print Immediate Window is used to debug and evaluate expressions, execute statements, print variable values, and so forth. It allows you to enter expressions to be evaluated or executed by the development language during debugging. To display the Immediate window, open a project for editing, then choose Windows from the Debug menu and select Immediate, or press CTRL+ALT+I.}

Answer 3

You can give this a try. This uses RegEx (Regular Expressions) to check for your date.

You will need to set a reference to the Microsoft VBScript Regular Expressions x.x

Sub ChangeDates()

    Dim RegEx As New RegExp, rng As Range, i As Long, s As String
    Dim tempArr() As String, bFlag As Boolean

    With RegEx
        .Pattern = "(\d{2})/(\d{2})/(\d{4})"
        For Each rng In ActiveSheet.UsedRange
            tempArr = Split(rng.Text)
            bFlag = False
            For i = 0 To UBound(tempArr)
                If .test(tempArr(i)) Then
                    s = tempArr(i)

                    'Subtract 1 year from original date
                    s = Format(DateAdd("YYYY", -1, CDate(s)), "MM/DD/YYYY")

                    'Swap month and day field
                    tempArr(i) = Format(DateSerial(.Replace(s, "$3"), _
                            .Replace(s, "$2"), .Replace(s, "$1")), "mm/dd/yyyy")

                    'Tell VBA that the string has change and to update sheet
                    bFlag = True
                End If
            Next
            If bFlag = True Then rng.Value = Join(tempArr)
        Next rng
    End With

End Sub  

Breaking down the RegEx pattern: (\d{2})/(\d{2})/(\d{4})

This expression is divided into three groups: (\d{2}), (\d{2}), (\d{4})

Group 1 and 2 finds any two ({2}) digits (\d), followed by a forward slash /

Group 3 is looking for any four ({4}) digits (\d) that comes after a forward slash /