|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 拆分()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim ar As Variant
Dim d As Object, dc As Object
Set d = CreateObject("scripting.dictionary")
For Each sh In Sheets
If sh.Index > 2 Then
sh.Delete
End If
Next sh
With Sheets("数据")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 2 Then MsgBox "数据为空!": End
ar = .Range("a1:f" & r)
End With
For i = 2 To UBound(ar)
If ar(i, 6) <> "" Then
If Not d.exists(ar(i, 6)) Then Set d(ar(i, 6)) = CreateObject("scripting.dictionary")
d(ar(i, 6))(ar(i, 2)) = d(ar(i, 6))(ar(i, 2)) + ar(i, 5)
End If
Next i
For Each k In d.keys
Sheets("结算表").Copy after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = k
.[c4] = k
For Each kk In d(k).keys
n = 0: hj = 0
ReDim br(1 To UBound(ar), 1 To 5)
For i = 2 To UBound(ar)
If ar(i, 6) = k And ar(i, 2) = kk Then
n = n + 1
For j = 1 To 5
br(n, j) = ar(i, j)
Next j
hj = hj + ar(i, 5)
End If
Next i
rs = .Cells(Rows.Count, 2).End(xlUp).Row + 1
.Cells(rs, 1).Resize(n, UBound(br, 2)) = br
.Cells(rs, 1).Resize(n + 1, UBound(br, 2) + 2).Borders.LineStyle = 1
.Cells(rs + n, 2) = "汇总"
.Cells(rs + n, 5) = hj
Next kk
ws = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(ws, 2) = "(物资公司业务部门盖章)"
.Cells(ws, 5) = "(物资公司财务部门盖章)"
.Cells(ws + 1, 2) = "业务部门负责人:"
.Cells(ws + 1, 5) = "财务部门负责人:"
End With
Next k
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "ok!"
End Sub
|
评分
-
1
查看全部评分
-
|