|
- Sub test()
- Application.ScreenUpdating = False
- Dim xlbook As Workbook, xlsheet As Worksheet, sht As Worksheet
- Dim FilePath As String, FileName As String
- Dim Arr(), Brr(), d As Object
- Set d = CreateObject("scripting.dictionary")
- Set sht = ThisWorkbook.Worksheets(1)
- FilePath = ThisWorkbook.Path & ""
- FileName = Dir(FilePath & "*.xlsx")
- Do While FileName <> ""
- Set xlbook = Workbooks.Open(FilePath & FileName)
- Set xlsheet = xlbook.Worksheets(1)
- Arr = xlsheet.UsedRange
- For i = 5 To UBound(Arr)
- If d.exists(Arr(i, 2)) Then
- d(Arr(i, 2)) = Array(Arr(i, 2), d(Arr(i, 2))(1) + Arr(i, 4))
- Else
- d(Arr(i, 2)) = Array(Arr(i, 2), Arr(i, 4))
- End If
- t = d(Arr(i, 2))
- Next i
- Erase Arr
- xlbook.Close False
- FileName = Dir
- Loop
- Brr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(d.items))
- sht.Cells(5, 2).Resize(UBound(Brr), UBound(Brr, 2)) = Brr
- Erase Brr
- Set xlbook = Nothing
- Set xlsheet = Nothing
- Set sht = Nothing
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|