EXCEL值计算

发布网友 发布时间：2022-06-07 14:59

我来回答

共4个回答

热心网友 时间：2023-10-19 01:15

方括号的代码对整个三列都生效，但忽略了a列为0的情况。修改如下：
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 1 Or Target.Column = 2 Then Cells(Target.Row, 3) = Cells(Target.Row, 1) * Cells(Target.Row, 2)
If Target.Column = 3 Then Cells(Target.Row, 2) = Cells(Target.Row, 3) / Cells(Target.Row, 1)
End Sub

如果只要对A1,B1,C1起作用，还需修改。
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 1 And (Target.Column = 1 Or Target.Column = 2) Then
[c1] = [a1] * [b1]
End If
If Target.Row = 1 And Target.Column = 3 Then
[b1] = [c1] / [a1]
End If
End Sub

热心网友 时间：2023-10-19 01:15

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 0 Or Range("A1").Value = "" Then
MsgBox "A1不能为零或空值"
Else
Select Case Target.Column
Case 1
Range("C1").Value = Range("A1").Value * Range("B1")
Case 2
Range("C1").Value = Range("A1").Value * Range("B1")
Case 3
Range("B1").Value = Range("C1").Value / Range("A1").Value
End Select
End If
End Sub

楼上的应该是可以的，只是A1是不能为0或空值的否则就会出错，建议加入条件判断A1的值取向，以免出错

热心网友 时间：2023-10-19 01:16

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column = 2 Then Cells(Target.Row, 3) = Cells(Target.Row, 1) * Cells(Target.Row, 2)
If Target.Column = 3 Then Cells(Target.Row, 2) = Cells(Target.Row, 3) / Cells(Target.Row, 1)
End Sub

热心网友 时间：2023-10-19 01:17

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
[c1] = [a1] * [b1]
ElseIf Target.Address = "$C$1" Then
[b1] = [c1] / [a1]
End If
End Sub