代码放在工作表“凭证”里,切记:
Range(Cells(2, 1), Cells([a1000].End(3).Row, 16)).ClearContents '清屏,可以重复统计
hs = 2 '凭证数据存放起始行
With Sheets("OA数据")
For i = 2 To .[i10000].End(3).Row
js = 0
s0 = .Cells(i, 1)
s1 = .Cells(i, "i")
s2 = .Cells(i, "k")
s3 = .Cells(i, 2) & "报" & .Cells(i, "l")
s4 = IIf(.Cells(i, "f") = "供应商", .Cells(i, "f") & "--" & .Cells(i, "g"), "")
s5 = .Cells(i, 2)
hj = s2
Cells(hs, 1) = Date
Cells(hs, 2) = Year(Date)
Cells(hs, 4) = "记"
Cells(hs, 5) = s0
Cells(hs, 6) = s1
Cells(hs, 8) = "RMB"
Cells(hs, 9) = "人民币"
Cells(hs, 10) = s2
Cells(hs, 11) = s2
Cells(hs, 13) = "徐梅"
Cells(hs, 14) = s3
Cells(hs, 15) = js
Cells(hs, 16) = s4
hs = hs + 1
For k = i + 1 To 50
If (.Cells(k, 2) = "") And (Not IsEmpty(.Cells(k, "i"))) Then
js = js + 1
hj = hj + Val(.Cells(k, "k"))
Cells(hs, 1) = Date
Cells(hs, 2) = Year(Date)
Cells(hs, 4) = "记"
Cells(hs, 5) = s0
Cells(hs, 6) = s1
Cells(hs, 8) = "RMB"
Cells(hs, 9) = "人民币"
Cells(hs, 10) = .Cells(k, "k")
Cells(hs, 11) = .Cells(k, "k")
Cells(hs, 13) = "徐梅"
Cells(hs, 14) = s3
Cells(hs, 15) = js
Cells(hs, 16) = s4
hs = hs + 1
Else
i = k - 1
Exit For
End If
Next k
js = js + 1
Cells(hs, 1) = Date
Cells(hs, 2) = Year(Date)
Cells(hs, 4) = "记"
Cells(hs, 5) = s0
Cells(hs, 6) = s1
Cells(hs, 8) = "RMB"
Cells(hs, 9) = "人民币"
Cells(hs, 10) = hj
Cells(hs, 12) = hj
Cells(hs, 13) = "徐梅"
Cells(hs, 14) = s3
Cells(hs, 15) = js
Cells(hs, 16) = s4
hs = hs + 1
x = Mid(x, 1, 11) & Trim(CStr(Val(Mid(x, 12, 6)) + 1))
Next i
End With
累死我了,不是有多复杂,是列字段太多,要费尽去对齐。 |