键值对，通过字典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