他山之石——使用VBA字典-Part2

最新推荐文章于 2018-06-26 23:58:12 发布

预见未来to50

最新推荐文章于 2018-06-26 23:58:12 发布

阅读量399

点赞数 2

分类专栏： Excel技巧及VBA编程（Excel_VBA）

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/80795652

版权

Excel技巧及VBA编程（Excel_VBA）专栏收录该内容

404 篇文章 169 订阅

订阅专栏

这里讲的三个字典的应用，都挺新鲜的，有机会可以实践一下。

Sub 提取不重复的产品()
 Dim d As New Dictionary
 Dim arr, x
 arr = Range("a2:a12")
 For x = 1 To UBound(arr)
      d(arr(x, 1)) = ""
 Next x
 Range("c2").Resize(d.Count) = Application.Transpose(d.Keys)
End Sub


Sub 汇总()
 Dim d As New Dictionary
 Dim arr, x
 arr = Range("a2:b10")
 For x = 1 To UBound(arr)
   d(arr(x, 1)) = d(arr(x, 1)) + arr(x, 2) 'key对应的item的值在原来的基础上加新的
 Next x
 Range("d2").Resize(d.Count) = Application.Transpose(d.Keys)
 Range("e2").Resize(d.Count) = Application.Transpose(d.Items)
End Sub

Sub 多表双向查找()
 Dim d As New Dictionary
 Dim x, y
 Dim arr
 For x = 3 To 5
   arr = Sheets(x).Range("a2").Resize(Sheets(x).Range("a65536").End(xlUp).Row - 1, 2)
   For y = 1 To UBound(arr)
     d(arr(y, 1)) = arr(y, 2)
     d(arr(y, 2)) = arr(y, 1)
   Next y
 Next x
 MsgBox d("C1")
 MsgBox d("吴情")
End Sub