|
各位高手:
我有一数据导入表格,数量很大,我使用某列数据不重复数组公式及VLOOKUP函数等公式,虽然能导入,
但是运行速度很慢,且在次重新打开工作簿时,总是出现:正在计算(2个处理器)*%,运行非常
缓慢,请教高手能否用宏代码解决,恳请高手帮助解决,谢谢
详见截图及附件
- Sub dy()
- Dim arr, brr, i&, n&, d As Object, k, it
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("汇总").[a1].CurrentRegion
- For i = 5 To UBound(arr)
- If arr(i, 3) <> "" Then d(arr(i, 3)) = d(arr(i, 3)) + arr(i, 13)
- Next
- k = d.keys: it = d.items
-
- With Sheets("导入")
- brr = .Range("e6:f" & .[e65536].End(3).Row)
- For i = 1 To UBound(brr)
- For n = 0 To d.Count - 1
- If brr(i, 1) = k(n) Then
- brr(i, 2) = it(n): Exit For
- End If
- Next
- Next
- .[f6].Resize(i - 1, 1).Clear
- .[f6].Resize(i - 1, 1) = Application.Index(brr, , 2)
- d.RemoveAll
- End With
- End Sub
复制代码
|
|