|
本帖最后由 一指禅62 于 2019-3-18 17:12 编辑
控制性代码
- Private Sub 生成记账单(key As String)
- Dim arr, i&, s$, d As Object
- Set d = CreateObject("Scripting.Dictionary")
- Rem 收集进(出)仓信息
- arr = Sheet1.Range("A1").CurrentRegion
- For i = 2 To UBound(arr)
- If arr(i, 7) = IIf(key = "进", "1243", "5101") Then
- s = Format(arr(i, 1), "yyyymm") & Format(arr(i, 3), "0000")
- If Not d.Exists(s) Then
- d(s) = i
- Else
- d.Item(s) = d.Item(s) & "|" & i
- End If
- End If
- Next
- Rem 逐号制作进(出)仓单
- Dim k, t, N%, R&
- Application.ScreenUpdating = False
- Range("A17:F65536").Delete Shift:=xlUp
- For Each k In d.keys
- N = N + 1
- R = 5
- Range("A5:F13") = ""
- Range("A2") = arr(t, 1)
- Range("F2") = Mid(k, 1, 6) & Format(N, "000")
- Range("A3") = Split(arr(t, 6), " ")(0)
- For Each t In Split(d.Item(k), "|")
- Range("A" & R) = Replace(arr(t, 8), IIf(key = "进", "库存商品_", "主营业务收入_"), "")
- Range("B" & R) = ""
- Range("C" & R) = "吨"
- Range("D" & R) = arr(t, 20)
- Range("E" & R) = arr(t, 21)
- Range("F" & R) = arr(t, 22)
- R = R + 1
- Next
- Range("A1:F16").Copy
- Range("A65536").End(3).Offset(3, 0).PasteSpecial
- Next
- If N > 1 Then Range("A1:F18").Delete Shift:=xlUp
- Application.ScreenUpdating = True
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|