|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
第一行累计在租量为何不减去出库数量呢
- Option Explicit
- Sub 台帐()
- Dim d As Object, arr(), lastRow&, i&, k&, m&, results(), 日期 As Date, 开始日期 As Date, 结束日期 As Date
- Dim 租赁单位$, 品名, 单位$, 入库数量#, 出库数量#, 单位租金#, 总入库#, 总出库#, 金额小计#, 金额合计#
- Set d = CreateObject("scripting.dictionary")
- With Sheets("租赁台帐")
- lastRow = .Cells(Rows.Count, "a").End(xlUp).Row
- arr = .Range("a3:h" & lastRow).Value
- End With
-
- For i = 1 To UBound(arr)
- 品名 = arr(i, 4)
- 单位 = arr(i, 5)
- 日期 = Format(arr(i, 2), "yyyy/mm/dd")
- 入库数量 = arr(i, 7)
- 出库数量 = arr(i, 8)
- 单位租金 = arr(i, 6)
- 租赁单位 = arr(i, 3)
-
- If Not d.exists(租赁单位) Then
- Set d(租赁单位) = CreateObject("scripting.dictionary")
- Set d(租赁单位)(品名) = New Collection
- d(租赁单位)(品名).Add Array(品名, 单位, 日期, 入库数量, 出库数量, 单位租金)
- Else
- If Not d(租赁单位).exists(品名) Then
- Set d(租赁单位)(品名) = New Collection
- d(租赁单位)(品名).Add Array(品名, 单位, 日期, 入库数量, 出库数量, 单位租金)
- Else
- d(租赁单位)(品名).Add Array(品名, 单位, 日期, 入库数量, 出库数量, 单位租金)
- End If
- End If
- Next
-
- ReDim results(1 To lastRow * 2, 1 To 10)
- With Sheets("结算清单生成")
- 租赁单位 = .[B2]
- 开始日期 = Format(.[G2], "yyyy/mm/dd")
- 结束日期 = Format(.[J2], "yyyy/mm/dd")
- If Not d.exists(租赁单位) Then
- MsgBox 租赁单位 & "不存在,请检查!"
- Exit Sub
- End If
-
- m = 1
- For Each 品名 In d(租赁单位).keys
- k = m
- For i = 1 To d(租赁单位)(品名).Count
- If d(租赁单位)(品名)(i)(2) < 开始日期 Then
- results(m, 4) = results(m, 4) + d(租赁单位)(品名)(i)(3)
- results(m, 5) = results(m, 5) + d(租赁单位)(品名)(i)(4)
- End If
- Next
- results(m, 1) = 品名
- results(m, 2) = d(租赁单位)(品名)(1)(1)
- results(m, 3) = 开始日期 - 1
- results(m, 6) = d(租赁单位)(品名)(1)(5)
- results(m, 7) = 结束日期 - 开始日期 + 1
- results(m, 8) = (results(m, 4) - results(m, 5)) * results(m, 6) * results(m, 7)
- results(m, 9) = results(m, 4)
- 总入库 = results(m, 4)
- 总出库 = results(m, 5)
- 金额小计 = results(m, 8)
-
- For i = 1 To d(租赁单位)(品名).Count
- If d(租赁单位)(品名)(i)(2) <= 结束日期 And d(租赁单位)(品名)(i)(2) >= 开始日期 Then
- k = k + 1
- results(k, 1) = 品名
- results(k, 2) = d(租赁单位)(品名)(i)(1)
- results(k, 3) = d(租赁单位)(品名)(i)(2)
- results(k, 6) = d(租赁单位)(品名)(i)(5)
- results(k, 7) = 结束日期 - results(k, 3)
- results(k, 4) = d(租赁单位)(品名)(i)(3)
- results(k, 5) = d(租赁单位)(品名)(i)(4)
- results(k, 8) = (results(k, 4) - results(k, 5)) * results(k, 6) * results(k, 7)
- results(k, 9) = results(k - 1, 9) + results(k, 4) - results(k, 5)
- 总入库 = 总入库 + results(k, 4)
- 总出库 = 总出库 + results(k, 5)
- 金额小计 = 金额小计 + results(k, 8)
- End If
- Next
- k = k + 1
- results(k, 1) = 品名 & "小计"
- results(k, 4) = 总入库
- results(k, 5) = 总出库
- results(k, 8) = 金额小计
- 金额合计 = 金额合计 + 金额小计
- m = k + 1
- Next
- k = k + 1
- results(k, 1) = "本期租金合计"
- results(k, 4) = 金额合计
-
- .[a5].Resize(.Cells(Rows.Count, 1).End(xlUp).Row, 10).Clear
- .[a5].Resize(k, 10) = results
-
- i = 5
- Do While .Cells(i, 1) <> ""
- If .Cells(i, 1) Like "*小计" Or .Cells(i, 1) Like "*合计" Then
- .Cells(i, 1).Resize(1, 3).Merge
- End If
- i = i + 1
- Loop
-
- With .[a5].Resize(k, 10)
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .Font.Name = "黑体"
- .Borders.LineStyle = xlContinuous
- End With
- End With
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|