VBA 字典 键值为二维数组并不断增加行

Sub test()
Dim arr()
arr = Range("A1:F20") '也可dim area as range, set area = range("A1:F20")
Set dic1 = CreateObject("Scripting.dictionary") '必须先声明dic1为字典
Set dic1 = dicFromArr(arr) '必须是set 赋值,不然报错

For Each PN In Array("A", "B", "C")
    Row = Row + 5
    frr = dic1(PN)
    Cells(Row, "J").Resize(UBound(frr, 1), UBound(frr, 2)) = frr
Next
End Sub

尝试将 相同键的值写入同一个数组中,通过新建数组,遍历复制旧键值,遍历写入新键值的方法写入新键值数组,方法很呆.

常用方法: 只在键值中记录键在原数组中的坐标, 如键A在原数组中位于irow行,则需要的数据直接调用原数组的dic("A") = irow行.

复制新增数组的方法:

'方法1 transpose + redim preserve
'导出旧键值,转置是为了redim扩容
brr =application.transpose (dic("A"))
'转置的旧键值新增1列
redim preserve brr( 1 to ubound(brr,1) ,1 to ubound(brr,2)+1)
'原数组 新行 填入 
for j = 1 to ubound(brr,2)
    brr(j, ubound(brr,2)+1) = arr(i,j)