|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 生成出库单()
Application.ScreenUpdating = False
Dim ar As Variant
Dim i As Long, r As Long, rs As Long
Dim br()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("材料出入库汇总表")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 6 Then MsgBox "材料出入库汇总表工作表为空!": End
ar = .Range("a5:p" & r)
End With
For i = 2 To UBound(ar)
If ar(i, 14) <> "" And ar(i, 15) <> "" Then
zd = ar(i, 14) & "|" & ar(i, 15)
If Not d.exists(zd) Then Set d(zd) = CreateObject("scripting.dictionary")
d(zd)(i) = ""
End If
Next i
Set sh = Sheets("模板")
With Sheets("出入库单")
.UsedRange.Clear
For Each k In d.keys
n = 0: hj = 0
ReDim br(1 To d(k).Count, 1 To 8)
For Each kk In d(k).keys
n = n + 1
br(n, 1) = n
For j = 2 To 4
br(n, j) = ar(kk, j)
Next j
br(n, 5) = ar(kk, 10)
br(n, 6) = ar(kk, 5)
br(n, 7) = ar(kk, 11)
br(n, 8) = ar(kk, 14)
rq = ar(kk, 15)
hj = hj + ar(kk, 11)
Next kk
rs = .Cells(Rows.Count, 1).End(xlUp).Row + 3
If rs = 4 Then
rs = 1
Else
rs = rs
End If
sh.Rows("1:4").Copy .Cells(rs, 1)
.Cells(rs + 2, 4) = rq
.Cells(rs + 4, 1).Resize(n, UBound(br, 2)) = br
.Cells(rs + 4, 1).Resize(n, UBound(br, 2)).Borders.LineStyle = 1
sh.Rows("15:16").Copy .Cells(n + rs + 4, 1)
.Cells(n + rs + 4, 7) = hj
Next k
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|