Forum Discussion

jxw1101's avatar
jxw1101
Copper Contributor
Aug 25, 2022
Solved

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

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. 

excel
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Aug 25, 2022

    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.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 25, 2022

    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.

Resources