键值对,通过字典vba循环 [英] key value pair, loop through a dictionary vba
本文介绍了键值对,通过字典vba循环的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图一次读取一列一个单元格,并将该单元格存储为键,并将其频率存储为其值.然后,我想将所有键值对放入一个范围,例如列P和Q.我想我用下面的代码完成了工作的第一部分(虽然不是100%),现在如何放置键值对到一定范围?
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?
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
非常感谢您的帮助
推荐答案
尝试以下代码:
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
这篇关于键值对,通过字典vba循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文