VBA 数据字典练习

Sub zidian()
Dim Dic, arr, arr2, arr3, arr4, Dic1, Dic2
Set Dic = CreateObject("scripting.dictionary")
Set Dic1 = CreateObject("scripting.dictionary")
Set Dic2 = CreateObject("scripting.dictionary")
maxh = Range("A65536").End(xlUp).Row
arr = Range("A2:K" & maxh).Value
For i = 1 To UBound(arr)
If Dic.exists(arr(i, 1)) Then
    Dic1(arr(i, 1)) = Dic1(arr(i, 1)) + 1
    Dic(arr(i, 1)) = Dic.Item(arr(i, 1)) & " " & Dic1(arr(i, 1)) & "、" + arr(i, 11)
    Dic2(arr(i, 1)) = Dic2.Item(arr(i, 1)) & " " & Dic1(arr(i, 1)) & "、" + arr(i, 10)
Else
    Dic1(arr(i, 1)) = 1
    Dic(arr(i, 1)) = "1、" + arr(i, 11)
    Dic2(arr(i, 1)) = "1、" + arr(i, 10)
    
End If
Next
arr2 = Dic.keys
arr3 = Dic.items
arr4 = Dic2.items
Dic.RemoveAll
Dic1.RemoveAll
Dic2.RemoveAll
Set Dic = Nothing
Set Dic1 = Nothing
Set Dic2 = Nothing

Sheets("Sheet2").Range("A2:C65536").Clear
Sheets("Sheet2").Range("A2:A" & UBound(arr2) + 2).Value = Application.Transpose(arr2)
 For i = 0 To UBound(arr4)
 
 Sheets("Sheet2").Range("B" & i + 2).Value2 = arr4(i)

 Next

  For i = 0 To UBound(arr4)
    Sheets("Sheet2").Range("C" & i + 2).Value2 = arr3(i)
    Next


End Sub