displaying number of times a word occurs in a table column with VBA

Hi

just add this to a VBA Module, or the sheet code and run it, it will check all the cells of the active sheet for a specific text and tell you how many times it occurs, not case sensitive.....

Public Sub CountText()
    Dim Arr As Variant
    Dim i As Long, j As Integer
    Dim ST As String
    Dim STLen As Integer
    Dim FindIndex As Integer
    Dim StopSearch As Boolean
    Dim Cnt As Long
    Dim S1 As String
    
    ST = LCase(InputBox("Find"))
    
    If Trim(ST) = vbNullString Then Exit Sub
    
    Arr = ActiveSheet.UsedRange.Value

    For i = LBound(Arr, 1) To UBound(Arr, 1)
        For j = LBound(Arr, 2) To UBound(Arr, 2)
            If STLen < Len(Arr(i, j)) Then
                
                FindIndex = 0
                StopSearch = False
                S1 = LCase(Arr(i, j))
                
                While Not StopSearch
                    FindIndex = InStr(FindIndex + 1, S1, ST)
                    If FindIndex > 0 Then
                        Cnt = Cnt + 1
                    Else
                        StopSearch = True
                    End If
                Wend
            End If
        Next j
    Next i
    
    MsgBox "Found " & Cnt & " time(s)"
End Sub

should give you what you want, if you need help just post back, someone will help

Option Explicit

Sub count()
Dim term As String
term = "*" & InputBox("searched term:") & "*"
MsgBox Application.WorksheetFunction.CountIf(ActiveSheet.UsedRange, term)

End Sub