count the number of times a specific word appears in a range of cells

[Rossjp]

In a range of cells, a1:f1 specific words may be entered. I want a siple count of the number of times a specific word appears in this range at cell G1. Any assistance will be greatly appreciated.

 

[AlanY]

try

Book1
	A	B	C	D	E	F	G	H
1	John		John John				John	3
Sheet15

Cell Formulas
Range	Formula
H1	=SUMPRODUCT((LEN(A1:F1)-LEN(SUBSTITUTE(A1:F1,$G$1,"")))/LEN($G$1))

 

[alansidman]

Option Explicit


Sub CountWord()
    Dim x As Long
    x = 0
    Dim rng As Range
    Dim c As Range
    Set rng = Range("A1:F1")
    Dim crit As String
    crit = InputBox("What word to search")
    For Each c In rng
        If InStr(c, crit) > 0 Then
            x = x + 1
        End If
    Next c
    Range("G1") = x


End Sub