- Sub 打印()
- Rem http://club.excelhome.net/thread-1512767-1-1.html
- Dim arr, brr
- Dim m%, n%, i%
- Dim dic As Object
- Set dic = CreateObject("Scripting.Dictionary")
- arr = Worksheets("每箱数量").Range("B2").CurrentRegion
- brr = Worksheets("打印数据表").Range("B2").CurrentRegion
- For m = 1 To UBound(arr, 1)
- dic(arr(m, 1)) = arr(m, 2) '写入字典中方便后续查找
- Next
- With Worksheets("标签格式")
- For m = 2 To UBound(brr, 1)
- Union(.Cells(4, "C"), .Cells(25, "C"), .Cells(4, "K"), .Cells(25, "K")) = brr(m, 2)
- Union(.Cells(6, "C"), .Cells(27, "C"), .Cells(6, "K"), .Cells(27, "K")) = brr(m, 3)
- Union(.Cells(8, "C"), .Cells(29, "C"), .Cells(8, "K"), .Cells(29, "K")) = brr(m, 4)
- Union(.Cells(10, "C"), .Cells(31, "C"), .Cells(10, "K"), .Cells(31, "K")) = brr(m, 5)
- Union(.Cells(18, "C"), .Cells(39, "C"), .Cells(18, "K"), .Cells(39, "K")) = brr(m, 8)
- If brr(m, 6) > dic(brr(m, 2)) Then
- n = brr(m, 6) \ dic(brr(m, 2)) '获取整数份数
- i = brr(m, 6) Mod dic(brr(m, 2)) '多出来的非标准数量
- If n \ 4 > 0 Then '如果数量大于等于4份
- Union(.Cells(14, "C"), .Cells(35, "C"), .Cells(14, "K"), .Cells(35, "K")) = dic(brr(m, 2)) & "(" & brr(m, 7) & ")"
- .PrintOut Copies:=n \ 4 '先直接打印对应张纸
- End If
- If (n Mod 4 = 0) And (i > 0) Then
- .Cells(14, "C") = i & "(" & brr(m, 7) & ")"
- .Range("A1:D19").PrintOut Copies:=1, Collate:=True '然后按照选定区域打印一份
- ElseIf n Mod 4 = 1 Then
- .Cells(14, "C") = dic(brr(m, 2)) & "(" & brr(m, 7) & ")"
- If i > 0 Then .Cells(14, "K") = i & "(" & brr(m, 7) & ")"
- IIf(i > 0, .Range("A1:L19"), .Range("A1:D19")).PrintOut Copies:=1, Collate:=True '然后按照选定区域打印一份
- ElseIf n Mod 4 = 2 Then
- Union(.Cells(14, "C"), .Cells(14, "K")) = dic(brr(m, 2)) & "(" & brr(m, 7) & ")"
- If i > 0 Then
- .Cells(35, "C") = i & "(" & brr(m, 7) & ")"
- .Cells(35, "K") = "多余的一份,请作废!"
- End If
- IIf(i > 0, .Range("A1:L40"), .Range("A1:L19")).PrintOut Copies:=1, Collate:=True '然后按照选定区域打印一份
- ElseIf n Mod 4 = 3 Then
- Union(.Cells(14, "C"), .Cells(14, "K"), .Cells(35, "C")) = dic(brr(m, 2)) & "(" & brr(m, 7) & ")"
- .Cells(35, "K") = IIf(i > 0, i & "(" & brr(m, 7) & ")", "多余的一份,请作废!")
- .PrintOut Copies:=1, Collate:=True '然后按照选定区域打印一份
- End If
- Else
- Union(.Cells(14, "C"), .Cells(35, "C"), .Cells(14, "K"), .Cells(35, "K")) = brr(m, 6) & "(" & brr(m, 7) & ")"
- .Range("A1:D19").PrintOut Copies:=1, Collate:=True '按照选定区域打印一份
- End If
- Next
- End With
- MsgBox "打印已完成!"
- End Sub
复制代码 |