Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
Function CountWords(WordList As Variant) As Variant
Dim WordCount As Scripting.Dictionary
Dim NumRows As Long, ItemVal As Long, sWord As Variant, RtnA() As Variant
Dim i As Long, NumRes As Long, key As Variant
' Convert WordLIst to variant array
WordList = WordList.Value2
NumRows = UBound(WordList)
' Set up WordCount dictionary
Set WordCount = New Scripting.Dictionary
For i = 1 To NumRows
sWord = WordList(i, 1)
If sWord <> Empty Then
If WordCount.Exists(key:=sWord) = False Then
WordCount.Add sWord, 1
Else
ItemVal = WordCount.Item(sWord) + 1
WordCount.Remove sWord
WordCount.Add sWord, ItemVal
End If
End If
Next i
NumRes = WordCount.Count
ReDim RtnA(1 To NumRes, 1 To 2)
i = 1
For Each key In WordCount.Keys:
RtnA(i, 1) = key
RtnA(i, 2) = WordCount.Item(key)
i = i + 1
Next
Set WordCount = Nothing
CountWords = RtnA
End Function
Pivot Table is much easier.