Jan 26 2021 12:16 PM
Jan 26 2021 12:16 PM
In VBA I need to change all instances of a certain text value in a workbook to a new value, but I also need to know the number of changes made (instances of text at start). I have done some searching which talked about the CountIF function and I have the following code.
Dim objSheet As Worksheet Dim lngChanges As Long Dim rngAllCells As Range lngChanges = 0 For Each objSheet In ActiveWorkbook.Worksheets Set rngAllCells = Worksheets(objSheet.Name).Cells lngChanges = lngChanges + WorksheetFunction.CountIf(rngAllCells, "*My Old Text*") objSheet.Cells.Replace What:="My Old Text", Replacement:="My New Text", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False Next objSheet
Now it does count all text cells that contain my specified value.
The problem occurs if my text value is part of a formula.
i.e. =VLOOKUP("My Old Text", . . . )
Now I do want the text to be replaced in formulas and the Replace command above does that nicely.
But the CountIF function does not look in formulas, only in values, so the number of affected changes (in lngChanges) is incorrect.
Does anyone have suggestions on how to get the number of changes made correctly?
Jan 26 2021 01:14 PM
Maybe that will help you too, I don't know, because I know that I don't know anything.
Sub searchWord () Dim last line Dim last column Dim wordCounter As Integer lastline = ActiveSheet.Cells.SpecialCells (xlCellTypeLastCell) .Row last column = ActiveSheet.Cells.SpecialCells (xlCellTypeLastCell) .Column For i = 1 To last line For ii = 1 To last column 'Find the cell that contains my value and count 'wordCounter = wordCounter + 1 If InStr (1, Cells (i, ii) .Value, "text") Then wordCounter = wordCounter + 1 Else End If Next Next MsgBox wordCounter End Sub
Jan 26 2021 11:58 PM
Thank you for your response.
After sleeping on it (actually not sleeping but tossing and turning thinking about it :-)) I decided to completely abandon my approach of CountIF and objSheet.Cells.Replace so used the following.
Private Sub MyReplace(lngLookin As Long, lngChanges As Long, rngAllCells As Range) Dim rngCell As Range With rngAllCells Set rngCell = .Find(strFromDir, LookIn:=lngLookin) If Not rngCell Is Nothing Then Do 'While Not rngCell Is Nothing lngChanges = lngChanges + 1 If lngLookin = xlValues Then rngCell.Value = Replace(rngCell.Value, strFrom , strTo ) If lngLookin = xlFormulas Then rngCell.Formula = Replace(rngCell.Formula, strFrom, strTo ) Set rngCell = .FindNext(rngCell) Loop While Not rngCell Is Nothing End If 'Not rngCell Is Nothing End With 'rngAllCells End Sub
strFrom and strTo are global constants.
The procedure is called with the first parameter as either xlValues or xlFormulas (the builtin excel constants) to determine if it searches in values or formulas.
This seems to work