|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub test()
Dim Dic, Mp$, Fs$, Wb As Workbook, x%, c%, C1%, C2%, R&, k&, arr1, arr2(1 To 100, 1 To 2), kk%, kkk%, T#
T = Timer
Application.ScreenUpdating = False
Set Dic = CreateObject("scripting.dictionary")
Mp = ThisWorkbook.Path & "\"
Fs = Dir(Mp & "*.xlsx")
Do
Set Wb = Workbooks.Open(Mp & Fs)
With Wb
kk = kk + 1
For x = 1 To .Sheets.Count
kkk = kkk + 1
arr1 = .Sheets(x).Range("A1").CurrentRegion
For c = 1 To UBound(arr1, 2)
If arr1(1, c) = "对方号码" Then C1 = c
If arr1(1, c) = "实收通信费" Then C2 = c
Next c
For R = 2 To UBound(arr1)
If VBA.IsNumeric(arr1(R, C1)) = True Then
If Not Dic.exists(arr1(R, C1)) Then
k = k + 1
Dic(arr1(R, C1)) = k
arr2(k, 1) = arr1(R, C1)
arr2(k, 2) = arr1(R, C2)
Else
arr2(Dic(arr1(R, C1)), 2) = arr2(Dic(arr1(R, C1)), 2) + arr1(R, C2)
End If
End If
Next R
Next x
.Close False
End With
Set Wb = Nothing
Fs = Dir
Loop While Fs <> ""
With ThisWorkbook.Sheets(1)
.UsedRange.Offset(1).Clear
.[a2].Resize(UBound(arr2), 2) = arr2
End With
Application.ScreenUpdating = True
Cells(k + 1, 1) = "总计"
Cells(k + 1, 2) = Application.WorksheetFunction.Sum(Application.Index(arr2, 0, 2))
VBA.MsgBox "亲,已经汇总了 " & kk & " 个工作簿,计 " & kkk & " 张工作表," & Chr(10) & "累计用时" & Format(Timer - T, "0.000秒"), 16 * 4, "友情提醒"
End Sub
|
|