|
楼主 |
发表于 2012-9-3 11:46
|
显示全部楼层
hwc2ycy 发表于 2012-9-3 11:38
下午再帮你看,,有点事情得忙了。
谢谢你,我自已改了一下,只有“1001”运行正确,其它的就会出错,纠结之中。
Sub 总帐()
Dim SD As Date
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Set d3 = CreateObject("scripting.dictionary")
Application.ScreenUpdating = False
Sheets("总分类帐").Select
Range("a7:h65536").ClearContents
科目代码 = Range("c2")
SD = Range("e3")
sarr = Sheets("凭证记录").UsedRange
For I = 2 To UBound(sarr)
If sarr(I, 6) < SD Then
d3(sarr(I, 34)) = d3(sarr(I, 34)) + sarr(I, 19) - sarr(I, 20)
End If
Next I
Range("h6") = d3(科目代码)
Range("g6") = IIf(Range("h6") > 0, "借", IIf(Range("h6") < 0, "贷", "平"))
For r = 2 To UBound(sarr)
If sarr(r, 6) >= SD Then
y = Format(sarr(r, 6), "yyyy-mm-dd")
d2(Month(y)) = Application.Max(sarr(r, 4), d2(Month(y)))
If sarr(r, 34) = 科目代码 And sarr(r, 6) >= SD Then
d(y) = d(y) + sarr(r, 19)
d1(y) = d1(y) + sarr(r, 20)
End If
End If
Next r
If d.Count = 0 Then MsgBox "No data": Exit Sub
[a7].Resize(d.Count, 1) = Application.Transpose(d.keys)
[b7].Resize(d.Count, 1) = Application.Transpose(d.keys)
[c7].Resize(d.Count, 1) = Application.Transpose(d2.items)
[e7].Resize(d.Count, 1) = Application.Transpose(d.items)
[f7].Resize(d.Count, 1) = Application.Transpose(d1.items)
For I = 1 To d.Count
Cells(I + 6, 3) = "1 - " & Cells(I + 6, 3)
Cells(I + 6, 4) = "本月发生额"
Cells(I + 6, 8) = Round(Cells(I + 5, 8) + Cells(I + 6, 5) - Cells(I + 6, 6), 2)
s1 = s1 + Cells(I + 6, 5)
s2 = s2 + Cells(I + 6, 6)
Cells(I + 6, 7) = IIf(Cells(I + 6, 8) > 0, "借", IIf(Cells(I + 6, 8) < 0, "贷", "平"))
Next
Cells(7 + d.Count, 4) = "本年合计"
Cells(7 + d.Count, 5) = s1
Cells(7 + d.Count, 6) = s2
Cells(7 + d.Count, 8) = Cells(6 + d.Count, 8)
Cells(7 + d.Count, 7) = Cells(6 + d.Count, 7)
Application.ScreenUpdating = True
End Sub
20120902.rar
(50.18 KB, 下载次数: 10)
|
|