Adding a "Set Range" line to my function - Count Words in column

[makiwara]

Hi! I found a code on Stackoverflow (3. comment): https://stackoverflow.com/questions...s-in-an-excel-column-containing-a-lot-of-text


But I can't use, because if I add a "Set RangeToCheck= Range("A1:A4") it doesn't work.
If i click the "run" button, it asks for the macro's name


Hi could I give the range to this code? I have no idea! Have a very nice day!




"Usage: CountTheWordsInRange Range("A1:A4")

Sub CountTheWordsInRange(RangeToCheck As Range)
CountTheWordsInRange Range("A1:A4")
Dim wordList As New Collection
Dim keyList As New Collection
Dim c
For Each c In RangeToCheck
    Dim words As Variant
    words = Split(c, " ") 'Pick a delimiter
    For Each w In words
        Dim temp
        temp = -1
        On Error Resume Next
        temp = wordList(w)
        On Error GoTo 0
        If temp = -1 Then
            wordList.Add 1, Key:=w
            keyList.Add w, Key:=w
        Else
            wordList.Remove (w)
            keyList.Remove (w)
            wordList.Add temp + 1, w
            keyList.Add w, Key:=w
        End If
    Next w
Next c
'Here we can display the word counts
'KeyList is a collection that contains each word
'WordList is a collection that contains each amount
Dim x
For x = 1 To wordList.count
    With Sheets("Sheet1")
        .Cells(x, "E").Value = keyList(x)  'Display Word in column "E"
        .Cells(x, "F").Value = wordList(x) 'Display Count in column "F"
    End With
Next x


End Sub

Results:
"

 

[Fluff]

You need to call that sub like

Sub test()
CountTheWordsInRange Range("A1:A4")
End Sub