■ 設定
方法1
~~~~ Dim dic As Object Set dic = CreateObject("Scripting.Dictionary") ~~~~
方法2
~~~~ Dim dic As New Dictionary ~~~~ のように使うには、「ツール」→「参照設定」で、 「Microsoft Scripting Runtime」を参照設定する
■ サンプル
```` Sub ボタン1_Click() ' 定数 Const MaxRow As Integer = 5000 Dim sheet As Worksheet Set sheet = Worksheets("Sheet1")
' ★注目★
Dim countDictionary As New Dictionary
For i = 1 To MaxRow
If IsEmpty(sheet.Cells(i, 1).value) Then
' 値がない時は処理を中断
Exit For
End If
If Not countDictionary.Exists(sheet.Cells(i, 1).value) Then
' 値がない時はAdd
countDictionary.Add sheet.Cells(i, 1).value, 1
Else
countDictionary(sheet.Cells(i, 1).value) = countDictionary(sheet.Cells(i, 1).value) + 1
End If
Next
Dim valueDictionary As Variant
Dim index As Integer: index = 1
For Each keyDictionary In countDictionary
sheet.Cells(index, 4).value = keyDictionary
sheet.Cells(index, 5).value = countDictionary(keyDictionary)
index = index + 1
Next keyDictionary
End Sub
## 入力データ
Tom Tom Mike Tom Mike Smith Kevin Smith Tom
## 出力結果
Tom 4 Mike 2 Smith 2 Kevin 1
# 参考文献
すごく参考になった
http://excel-ubara.com/excelvba4/EXCEL216.html http://www.excel-wing.com/study/jitumu/970 # 関連記事 ** Excel マクロ ~ 入門編 ~ ** https://dk521123.hatenablog.com/entry/2015/07/15/104500