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
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
' 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 171
Downloads 53
CodeID
DB ID