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
apple | pear | lime | ||||||||
lime | orange | mango | ||||||||
pineapple | lime | apricot | assume A1:C4 is your entire worksheet | |||||||
banana | coconut | lime | ||||||||
how many times does "lime" occur ? | ||||||||||
this macro puts the 4 into cell A10 | ||||||||||
4 | For j = 1 To 4 | |||||||||
For k = 1 To 4 | ||||||||||
If Cells(j, k) = "lime" Then Sum = Sum + 1 | ||||||||||
Next k | ||||||||||
Next j | ||||||||||
Cells(10, 1) = Sum | ||||||||||
End Sub | ||||||||||