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 s...
excel
Macros and VBA
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