|
发表于 2015-4-28 16:45
|
显示全部楼层
本楼为最佳答案
- Sub 月结明细表()
- yf = 3
- kh = "宏星印刷有限公司"
- arr = Sheets("送货明细表").[a1].CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 12)
- xrr = Array(3, 2, 4, 5, 6, 7, 8, 10, 11, 12, 13)
- For i = 3 To UBound(arr)
- If arr(i, 1) = kh And Month(arr(i, 2)) = yf Then
- n = n + 1
- For j = 0 To UBound(xrr)
- brr(n, j + 1) = arr(i, xrr(j))
- Next
- End If
- Next
- If n = 0 Then MsgBox "无记录": Exit Sub
- Sheets("月结明细表").Copy after:=Sheets(Sheets.Count)
- With ActiveSheet
- .[a10:k42].ClearContents
- .[a10].Resize(n, 12) = brr
- .[b5] = kh
- .[e4] = yf
- .Name = Left(kh, 2) & yf & "月明细表"
- End With
- End Sub
- Sub 月结汇总表()
- yf = 3
- kh = "宏星印刷有限公司"
- arr = Sheets("送货明细表").[a1].CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 7)
- Set d = CreateObject("scripting.dictionary")
- For i = 3 To UBound(arr)
- If arr(i, 1) = kh And Month(arr(i, 2)) = yf Then
- dh = arr(i, 3)
- If Not d.exists(dh) Then
- n = n + 1
- d(dh) = n
- brr(n, 1) = n
- brr(n, 2) = arr(i, 2)
- brr(n, 4) = dh
- End If
- p = d(dh)
- brr(p, 7) = brr(p, 7) + arr(i, 12)
- End If
- Next
- If n = 0 Then MsgBox "无记录": Exit Sub
- Sheets("月结汇总表").Copy after:=Sheets(Sheets.Count)
- With ActiveSheet
- .[a10:L43].ClearContents
- .[a10].Resize(n, 7) = brr
- .[b5] = kh
- .[e4] = yf
- .Name = Left(kh, 2) & yf & "月汇总表"
- End With
- End Sub
复制代码 |
|