日常生活中,购入消耗货物常运用Excel进行相关统计,能不能实现自动统计出某类货物的结余情况呢?能。示例如下:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lj, jy, Ljgr, Ljxh
If Target.Row <= 1 Then Exit Sub
If Target.Column < 3 Or Target.Column > 7 Then Exit Sub
If Target.Count > 2 Then Exit Sub
'判断输入值非负数
If Target.Value < 0 And Target.Column > 3 And Target.Column < 6 Then
MsgBox "内容不能为负值!"
Target.Select
Target.Value = ""
End If
If Target.Row = 2 Then
Cells(2, 6) = Cells(2, 4) - Cells(2, 5)
End If
If Target.Row > 2 Then
Ljgr = 0
Ljxh = 0
For i = 2 To Target.Row - 1
If Cells(i, 3) = Cells(Target.Row, 3) Then
Ljgr = Ljgr + Cells(i, 4)
Ljxh = Ljxh + Cells(i, 5)
End If
Next
'判断消耗是否大于以往结余
If (Target.Value > Ljgr - Ljxh + Cells(Target.Row, 4)) And Target.Column = 5 Then
MsgBox "消耗过多了!"
Target.Value = ""
Target.Select
End If
Cells(Target.Row, 6) = Ljgr - Ljxh + Cells(Target.Row, 4) - Cells(Target.Row, 5)
End If
End Sub