Forum Discussion

Gilgamesh1964's avatar
Gilgamesh1964
Brass Contributor
Jan 26, 2021

How do I count all instances of a certain text string in a sheet

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

excel
Macros and VBA

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 26, 2021

    Gilgamesh1964 

    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

     

     

    • Gilgamesh1964's avatar
      Gilgamesh1964
      Brass Contributor
      Jan 27, 2021

      NikolinoDE 

      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

Resources