|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim ws As Worksheet
- Dim d As Object
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- n = 3
- For Each ws In Worksheets
- If Right(ws.Name, 1) = "年" Then
- d1(ws.Name) = n
- n = n + 3
- End If
- Next
- ls = 2 + d1.Count * 3
- For Each ws In Worksheets
- If Right(ws.Name, 1) = "年" Then
- n = d1(ws.Name)
- With ws
- r = .Cells(.Rows.Count, 2).End(xlUp).Row
- arr = .Range("a3:e" & r)
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 2)) Then
- ReDim brr(1 To ls)
- brr(2) = arr(i, 2)
- Else
- brr = d(arr(i, 2))
- End If
- brr(n) = brr(n) + arr(i, 3)
- brr(n + 2) = brr(n + 2) + arr(i, 5)
- d(arr(i, 2)) = brr
- Next
- End With
- End If
- Next
- ReDim crr(1 To d.Count, 1 To ls)
- ReDim drr(1 To ls)
- drr(2) = "合计"
- m = 0
- For Each aa In d.keys
- brr = d(aa)
- m = m + 1
- brr(1) = m
- For j = 1 To UBound(brr)
- crr(m, j) = brr(j)
- Next
- For j = 3 To UBound(brr)
- drr(j) = drr(j) + brr(j)
- Next
- Next
- For j = 3 To UBound(drr) Step 3
- If Len(drr(j)) <> 0 And drr(j) <> 0 Then
- drr(j + 1) = Application.Round(drr(j + 2) / drr(j), 2)
- End If
- Next
- With Worksheets("汇总表")
- .Cells.Clear
- With .Range("a1")
- .Value = "龙凤煤矿" & d1.keys()(0) & "--" & d1.keys()(d1.Count - 1) & "销售汇总表"
- .Resize(1, ls).Merge
- With .Font
- .Name = "宋体"
- .Size = 20
- .Bold = True
- End With
- End With
- .Range("a2:b2") = Array("序号", "煤炭品种")
- For j = 1 To 2
- .Cells(2, j).Resize(2, 1).Merge
- Next
- n = 3
- For Each aa In d1.keys
- With .Cells(2, n)
- .Value = aa & "销售汇总"
- .Resize(1, 3).Merge
- End With
- .Cells(3, n).Resize(1, 3) = Array("销售数量" & vbLf & "(吨)", "销售单价" & vbLf & "(元/吨)", "销售金额" & vbLf & "(元)")
- n = n + 3
- Next
- With .Range("a4").Resize(1, UBound(drr))
- .Value = drr
- With .Font
- .Bold = True
- End With
- End With
- .Range("a5").Resize(UBound(crr), UBound(crr, 2)) = crr
- With .Range("a2").Resize(2 + 1 + UBound(crr), UBound(crr, 2))
- .Borders.LineStyle = xlContinuous
- With .Font
- .Name = "宋体"
- .Size = 10
- End With
- End With
- With .UsedRange
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
- End Sub
复制代码 |
评分
-
2
查看全部评分
-
|