Jan 26 2021 12:16 PM
Hello,
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?
Thank You
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.
Nikolino
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