|
代码如下,请参考。。。
- Sub 汇总() '//2023.3.23
- Dim arr, brr, d
- Dim tm: tm = Timer
- p = ThisWorkbook.Path & ""
- f = Dir(p & "*.xls*")
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set sh = Sheet1
- b = sh.[a1:i1]
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Do While f <> ""
- If f <> ThisWorkbook.Name Then
- n = n + 1
- Set wb = GetObject(p & f)
- Set sht = wb.Sheets(1)
- arr = sht.UsedRange
- wb.Close False
- For i = 2 To UBound(arr)
- s = arr(i, 3)
- d(s) = arr(i, 2)
- s1 = arr(i, 3) & "|" & arr(1, 4)
- d1(s1) = arr(i, 4)
- Next
- End If
- f = Dir
- Loop
- ReDim brr(1 To d.Count, 1 To 10)
- For Each k In d.keys
- m = m + 1
- brr(m, 1) = m
- brr(m, 2) = d(k)
- brr(m, 3) = k
- Next
- With sh
- .UsedRange.Offset(1).Clear
- .[a2].Resize(m, 3) = brr
- Set Rng = .[a1].Resize(m + 1, 9)
- arr = Rng.Value
- For i = 2 To UBound(arr)
- For j = 4 To UBound(arr, 2)
- s = arr(i, 3) & "|" & arr(1, j)
- If d1.exists(s) Then
- arr(i, j) = d1(s)
- End If
- Next
- Next
- With Rng
- .Value = arr
- .Borders.LineStyle = 1
- .VerticalAlignment = xlCenter
- .HorizontalAlignment = xlCenter
- End With
- End With
- Application.ScreenUpdating = True
- MsgBox "汇总完毕,共用时:" & Format(Timer - tm, "0.00") & "秒"
- End Sub
复制代码
|
|