费用记录表如何使用VBA

S110018

有两个费用记录表，记录几台车辆的日常费用。填充蓝色的C、E、P、R列是日常记录列，现寻求如何写VBA码能够快速运算得出G、S列的余额。谢谢！

蓝桥玄霜

余额的例子：

外块300

Sub 计算()
x = Sheets("PP").UsedRange
For i = 1 To UBound(x)
If InStr(x(i, 1), "油卡号") Then
ye1 = x(i + 2, 7): ye2 = x(i + 2, 19): i = i + 3
End If
If x(i, 2) <> "" And InStr(x(i, 2), ".") Then x(i, 7) = ye1 - x(i, 3) + x(i, 5): ye1 = x(i, 7)
If x(i, 15) <> "" And InStr(x(i, 15), ".") Then x(i, 19) = ye2 - x(i, 16) + x(i, 18): ye2 = x(i, 19)
Next
Sheets("PP").[a1].Resize(UBound(x, 1), UBound(x, 2)) = x
End Sub

S110018

外块300 发表于 2024-2-4 09:57
Sub 计算()
x = Sheets("PP").UsedRange
For i = 1 To UBound(x)

谢谢！是否可以做成单元格触发事件？有业务发生录入金额，自动出余额来那种。

外块300

S110018 发表于 2024-2-5 23:52
谢谢！是否可以做成单元格触发事件？有业务发生录入金额，自动出余额来那种。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Cells(Target.Row, "g") = Range("g" & Target.Row - 1) - Target
If Target.Column = 5 Then Cells(Target.Row, "g") = Range("g" & Target.Row - 1) + Target
If Target.Column = 16 Then Cells(Target.Row, "s") = Range("s" & Target.Row - 1) - Target
If Target.Column = 18 Then Cells(Target.Row, "s") = Range("s" & Target.Row - 1) + Target
End Sub

S110018

外块300 发表于 2024-2-21 09:12
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Cells(Target.Row, " ...

谢谢您的帮助，给我指明了方向。