|
- Sub 求和()
- On Error Resume Next
- Dim wb As Workbook, sh As Worksheet
- Set wb = Workbooks.Open(ThisWorkbook.Path & "\多条件求和数据源.xls")
- Set wb = Workbooks("多条件求和数据源.xls")
- On Error GoTo 0
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Set d3 = CreateObject("scripting.dictionary")
- Set d4 = CreateObject("scripting.dictionary")
- Set d5 = CreateObject("scripting.dictionary")
- For Each sh In wb.Worksheets
- arr = sh.[a1].CurrentRegion
- For i = 2 To UBound(arr)
- If arr(i, 2) = "" Then arr(i, 2) = arr(i - 1, 2) '用上一格内容填齐空格部分(编码、名称、部门)
- If arr(i, 3) = "" Then arr(i, 3) = arr(i - 1, 3)
- If arr(i, 7) = "" Then arr(i, 7) = arr(i - 1, 7)
- je = arr(i, 6) '金额
-
- bm = arr(i, 7): lb = "类别" & arr(i, 4) '部门、类别
- k1 = bm & lb '部门+类别
- d1(k1) = d1(k1) + je
- If arr(i, 8) = "√" Then d2(k1) = d2(k1) + je
-
- mx = arr(i, 5) '明细
- k3 = bm & mx
- d3(k3) = d3(k3) + je
- If arr(i, 8) = "√" Then d4(k3) = d4(k3) + je
-
- k5 = arr(i, 2) & arr(i, 4) '编码+大类
- d5(k5) = d5(k5) + je
- Next
- Next
- wb.Close False
- For m = 1 To 5
- Set sh = ThisWorkbook.Sheets(m)
- brr = sh.[a1].CurrentRegion
- For i = 3 To UBound(brr)
- p = IIf(m = 5, 3, 2) '第五张表从第三列开始填,其他从第二列开始
- For j = p To UBound(brr, 2)
- k = brr(i, 1) & brr(2, j)
- If m = 1 Then brr(i, j) = d1(k)
- If m = 2 Then brr(i, j) = d2(k)
- If m = 3 Then brr(i, j) = d3(k)
- If m = 4 Then brr(i, j) = d4(k)
- If m = 5 Then brr(i, j) = d5(k)
- Next
- Next
- sh.[a1].CurrentRegion = brr
- Next
- End Sub
复制代码 |
|