Using VBA to Count the # of Times a Word Appears in a cell and display in another cell for big range

[jxw1101]

Hello I need help on how to tweak my code so that it will count the number of times my search word appears in a single cell and then display the result in a corresponding cell for a big range of data

Here is my code thus far:

Right now the code will only count how many times the search word "verification failed" appeared in the particular cell I selected and display in corresponding cell I2. I want to know if it's possible to search an entire column of data for example: H2:H1000 and have corresponding results display in I2:I1000. Any help would greatly be appreciated. 

[#2 · Accepted solution]

@jxw1101 

Sub wordscount()

Dim i As Long
Dim count As Long
Dim search_word  As String

search_word = Range("I1").Value

For i = 2 To 1000

count = ((Len(Cells(i, 8)) - Len(Replace(Cells(i, 8), search_word, ""))) / Len(search_word))
Cells(i, 9).Value = count

Next i

End Sub

You can try this code. In the attached file you can click the button in cell K2 to run the macro. In cell I1 you can dynamically enter a searchword.

[#3]

@OliverScheurich THANK YOU!!!