|
发表于 2015-5-12 11:08
|
显示全部楼层
本楼为最佳答案
- Sub 生成入库单()
- Set d = CreateObject("scripting.dictionary")
- For Each sh In Worksheets
- dw = Right(sh.[i2], 1)
- If InStr(sh.Name, "库单") = 0 Then
- arr = sh.Range("a4:j19")
- For i = 1 To UBound(arr)
- For k = 1 To 10 Step 5
- x = arr(i, k)
- If Len(x) > 0 Then
- xmonth = Split(x, ".")(0): xday = Split(x, ".")(1)
- x = DateSerial(2015, xmonth, xday)
- If arr(i, k + 2) > 0 Then d(x) = d(x) & "," & sh.Name & "," & dw & "," & arr(i, k + 2)
- End If
- Next
- Next
- End If
- Next
- n = -10: k = 0
- Application.ScreenUpdating = False
- With Sheets("入库单")
- .[a:f] = ""
- Set copyrng = .Range("J1:O10")
- For Each x In d.keys
- k = k + 1
- xrr = Split(d(x), ",")
- n = n + 11
- copyrng.Copy .Cells(n, 1)
- s = 0
- .Cells(n + 1, 1) = "编号:" & Format(k, "000")
- .Cells(n + 1, 3) = x
- For i = 1 To UBound(xrr) Step 3
- s = s + 1
- If s = 6 Then
- s = 1
- n = n + 11
- k = k + 1
- copyrng.Copy .Cells(n, 1)
- .Cells(n + 1, 1) = "编号:" & Format(k, "000")
- .Cells(n + 1, 3) = x
- End If
- .Cells(n + s + 2, 1) = s
- .Cells(n + s + 2, 2) = xrr(i)
- .Cells(n + s + 2, 3) = xrr(i + 1)
- .Cells(n + s + 2, 5) = xrr(i + 2)
- Next
- Next
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|