SwitchTextinCells

Switch text inside cells with another text.
This was needed to correct an bug we presented, we needed to go through large range of cells and replace any H12 found in any of these cells with H14, and at the same time, replace H14 with H12 without doing double replacement.
We could do Find and Replace 3 times, but we were too lazy, we came up with this function instead.
It will return all cells that have been modified so we can check them later.
If SwitchOption = 0, it acts like simple replace function, just replace text in any cell having FindWhat with ReplaceWith
If SwitchOption = 1, then does double find and replace, First replace cells having FindWhat with ReplaceWith, then replace ReplaceWith with FindWhat

CodeFunctionName
What is this?

Public

Tested

Original Work
Function SwitchTextinCells(Range2Loop, FindWhat, ReplaceWith, Optional SwicthOption = 1, Optional InSheet = "Active", Optional InWB = "This")
    ' Switches text found in cells by replacing one with another
    ' If SwitchOption = 0, it acts like simple replace function, just replace text in any cell having FindWhat with ReplaceWith
    ' If SwitchOption = 1, then does double find and replace, First replace cells having FindWhat with ReplaceWith, then replace ReplaceWith with FindWhat
    ' Returns list of cells changed
    ' Needs VBInstr
    Rett = ""
    DummyTempString = "{$_-ANmar-_$}" ' Any string that is not expected to be inside cell
    If InWB = "This" Then InWB = ThisWorkbook.Name
    If InSheet = "Active" Then InSheet = Workbooks(InWB).ActiveSheet.Name
    For Each Ce1 In Workbooks(InWB).Worksheets(InSheet).Range(Range2Loop).Cells
        Ce2 = Ce1.Value
        If Ce2 = "" Then GoTo NextCe
        If VBInstr(DummyTempString, Ce2) > 0 Then
            ' We should not find DummyTempString here, do something > change DummyTempString and try again?
           
            GoTo NextCe
        End If
        Ce3 = Replace(Ce2, FindWhat, DummyTempString)
        Ce3 = Replace(Ce3, ReplaceWith, FindWhat)
        Ce3 = Replace(Ce3, DummyTempString, ReplaceWith)
        If Ce3 < > Ce2 Then
            Rett = Rett & IIf(Rett > "", ",", "") & Ce1.Address(0, 0)
            Ce1.Value = Ce3
        End If
NextCe:
        DoEvents
    Next
    SwitchTextinCells = Rett
End Function

Range2Loop, FindWhat, ReplaceWith, Optional SwicthOption = 1, Optional InSheet = "Active", Optional InWB = "This"

Views 197

Downloads 84

CodeID
DB ID