|
Sub 销售单()
Application.ScreenUpdating = False
Dim ar As Variant
Dim rn As Range
Dim d As Object
Dim br()
Set d = CreateObject("scripting.dictionary")
With Sheets("数据")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 2 Then MsgBox "数据为空!": End
ar = .Range("a1:x" & r)
End With
For i = 2 To UBound(ar)
If ar(i, 1) <> "" Then
If Not d.exists(ar(i, 1)) Then Set d(ar(i, 1)) = CreateObject("scripting.dictionary")
d(ar(i, 1))(i) = ""
End If
Next i
Set rn = Sheets("模板").Rows("1:22")
With Sheets("结果")
.UsedRange.Clear
m = 1
For Each k In d.keys
n = 0
ReDim br(1 To UBound(ar), 1 To 29)
For Each kk In d(k).keys
n = n + 1
br(n, 1) = n
br(n, 2) = ar(kk, 10)
br(n, 7) = ar(kk, 11)
br(n, 17) = ar(kk, 12)
br(n, 19) = ar(kk, 13)
br(n, 22) = ar(kk, 14)
br(n, 25) = ar(kk, 15)
br(n, 28) = ar(kk, 16)
br(n, 29) = ar(kk, 17)
kh = ar(kk, 3)
rq = ar(kk, 8)
Next kk
If n <= 13 Then
rn.Copy .Cells(m, 1)
.Cells(m + 3, 3) = k
.Cells(m + 3, 10) = kh
.Cells(m + 3, 24) = rq
.Cells(m + 5, 1).Resize(n, UBound(br, 2)) = br
m = m + 22
Else
ss = 0
If n / 13 = Int(n / 13) Then
sl = n / 13
Else
sl = Int(n / 13) + 1
End If
For i = 1 To n Step 13
ss = ss + 1
rn.Copy .Cells(m, 1)
.Cells(m + 3, 3) = k
.Cells(m + 3, 10) = kh
.Cells(m + 3, 24) = rq
.Cells(m + 1, 30) = ss & " / " & sl
tt = m + 4
For s = i To i + 12
If s <= n Then
tt = tt + 1
For j = 1 To UBound(br, 2)
.Cells(tt, j) = br(s, j)
Next j
End If
Next s
m = m + 22
Next i
End If
Next k
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|