Sub test()
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
Next Cell
MsgBox Word_Count
End Sub