This will let you enter the word and then count how many cells it appears it. I've set it using a Range (albeit only 3 cells!)
However if a word appears in a cell twice it will only count is once.
It should also be case insensitive.
Hope that helps
Dim Search_word As String
Dim rng As Range
Dim Word_Count As Long
Word_Count = 0
Set rng = Sheets("Sheet1").Range("A1:A3")
Search_word = InputBox("Please enter the word that you are looking for")
For Each Cell In rng.Cells
If InStr(1, LCase(Cell.Value), LCase(Search_word)) > 0 Then Word_Count = Word_Count + 1