|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub 编制现金账()
- Dim arr, i&, a(), n&, Rng As Range, M
- Rem 清除旧有现金账数据,填写上年结转数据
- Set Rng = Sheet1.Range("B:B").Find("库存现金", lookat:=xlWhole)
- If Not Rng Is Nothing Then M = Rng.Offset(0, 3).Value
- Sheet15.Range("A5:K65536").ClearContents
- Sheet15.Range("F5") = "上年结转"
- Sheet15.Range("K5") = M
- Rem 编制当期现金日记账
- arr = Sheets("凭证库").Range("A1").CurrentRegion
- For i = 2 To UBound(arr)
- If arr(i, 5) = "库存现金" Then
- n = n + 1: ReDim Preserve a(1 To 11, 1 To n)
- M = M + arr(i, 7)
- a(1, n) = arr(i, 13) '月
- a(2, n) = arr(i, 14) '日
- a(4, n) = arr(i, 15) '号
- a(5, n) = arr(i, 9) '对方科目
- a(6, n) = arr(i, 3) '摘要
- a(7, n) = "现收" '类别
- a(8, n) = arr(i, 7) '借方
- a(11, n) = M '余额
- ElseIf arr(i, 9) = "库存现金" Then
- n = n + 1: ReDim Preserve a(1 To 11, 1 To n)
- M = M - arr(i, 11)
- a(1, n) = arr(i, 13) '月
- a(2, n) = arr(i, 14) '日
- a(4, n) = arr(i, 15) '号
- a(5, n) = arr(i, 5) '对方科目
- a(6, n) = arr(i, 3) '摘要
- a(7, n) = "现付" '类别
- a(9, n) = arr(i, 11) '贷方
- a(11, n) = M '余额
- End If
- Next
- If n > 0 Then Sheet15.Range("A6").Resize(n, UBound(a)) = WorksheetFunction.Transpose(a)
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|