|
|
Sub 对账函()
Application.ScreenUpdating = False
With Sheets("销售明细")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 2 Then MsgBox "销售明细为空！": End
ar = .Range("a1:s" & r)
End With
With Sheets("9.30止欠款")
rs = .Cells(Rows.Count, 1).End(xlUp).Row
If rs < 2 Then MsgBox "9.30止欠款为空！": End
br = .Range("a2:h" & rs)
End With
With Sheets("对账函")
ws = .Cells(Rows.Count, 1).End(xlUp).Row
If ws >= 13 Then .Rows("13:" & ws).Delete
m = 13
For i = 2 To UBound(br)
n = 0: ds = 0: je = 0: zf = ""
If Trim(br(i, 2)) <> "" Then
mc = Trim(br(i, 2))
For s = 2 To UBound(ar)
If Trim(ar(s, 3)) = mc Then
n = n + 1
ds = ds + ar(s, 7)
je = je + ar(s, 13)
End If
Next s
If br(i, 3) < 0 Then
sy = "余款" & br(i, 3)
Else
sy = "欠款" & br(i, 3)
End If
zf = "上月贵公司欠款：" & sy & "。本月贵公司提货" & n & "车," & ds & "吨，货款" & je & "元。本月对公收款" & br(i, 5) & "。本月退款" & br(i, 6) & "。本月退款0元。截止2022年9月30日，贵公司在我司账户欠款:"
If i = 2 Then
.[a2] = mc
.[b4] = zf
.[d7] = br(i, 8)
Else
.Rows("1:12").Copy .Cells(m, 1)
.Cells(m + 1, 1) = mc
.Cells(m + 3, 2) = zf
.Cells(m + 6, 4) = br(i, 8)
m = m + 12
End If
End If
Next i
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2022-12-31 10:02
楼主
