键值对，遍历字典 vba
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16067965/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
key value pair, loop through a dictionary vba
提问by yatici
I am trying to read a column one cell at a time and store it the cell as a key and its frequency as its value. Then I want to place all key-value pairs into a range say column P and Q. I think I got the first part of the job done with the code below (not 100% on it though) Now how can place the key value pairs to a range?
我试图一次读取一个单元格的列，并将单元格存储为键，并将其频率存储为值。然后我想把所有的键值对放在一个范围里，比如列 P 和 Q。我想我用下面的代码完成了工作的第一部分（虽然不是 100%） 现在如何放置键值对到一个范围？
Dim D As Dictionary
Set D = New Dictionary
Dim DR As Range
Set DR = Range(Cells(2, 2), Cells(2, 2).End(xlDown))
For Each Cell In DR.Cells
If Not D.Exists(Cell.Value) Then
D.Add Cell, 1
Else
D.Exists (Cell.Value)
D.Item(Cell.Value) = D.Item(Cell.Value) + 1
End If
Next Cell
I roughly have the idea of looping through the dictionary per each key but I cant do
我粗略的想法是循环遍历每个键的字典，但我做不到
Dim k as key
any help is much appreciated
任何帮助深表感谢
回答by Santosh
Try below code :
试试下面的代码：
Sub test()
Dim D As Dictionary
Set D = New Dictionary
Dim DR As Range
Dim lastRow As Long
lastRow = Range("A65000").End(xlUp).Row
Set DR = Range("A2:A" & lastRow)
For Each Cell In DR
If D.Exists(CStr(Cell.Value)) = False Then
D.Add CStr(Cell.Value), 1
Else
D.Exists (Cell.Value)
D.Item(Cell.Value) = D.Item(Cell.Value) + 1
End If
Next
i = 2
For Each Key In D
Range("P" & i).Value = Key
Range("Q" & i).Value = D(Key)
i = i + 1
Next
End Sub
