|
- Sub test()
- Dim Wb As Workbook, Sh As Worksheet
- Set fso = CreateObject("scripting.filesystemobject")
- Set ff = fso.getfolder(ThisWorkbook.Path)
- Set d = CreateObject("scripting.dictionary")
- For Each fff In ff.subfolders '读入数据
- For Each F In fff.Files
- Set Wb = Workbooks.Open(F)
- Set Sh = Wb.Worksheets(1)
- arr = Sh.[a1].CurrentRegion
- frr = Split(F, "")
- yf = Split(frr(UBound(frr)), ".")(0) '取得月份数
- For i = 7 To UBound(arr)
- x = yf & arr(i, 2) '月份+项目为key
- d(x) = arr(i, 3)
- Next
- Wb.Close False
- Next
- Next
- With ActiveSheet
- .[c7].Resize(100, 100).ClearContents
- arr = .[a1].CurrentRegion
- For i = 7 To UBound(arr)
- s = 0
- For j = 3 To UBound(arr, 2) - 1
- x = arr(4, j) & arr(i, 2)
- arr(i, j) = d(x)
- s = s + Val(arr(i, j))
- Next
- arr(i, j) = s '最后一列合计
- Next
- .[a1].CurrentRegion = arr
- End With
- End Sub
复制代码 |
|