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...
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