excel - 如果用户更改范围内单元格的任何值，则计算范围内的单元格

Question

我有这段代码可以工作并根据 A2:A5 中的内容计算 A1。如果我在 A2:A5 中有“1”，那么 A1 是 4。现在我将 A1 更改为“3”并想看看 A2:A5 会是什么，但它会反弹回“4”。我如何修改这个宏来实现这一点？我需要它适用于 A1:A5 范围内的任何单元格。

Private Sub Worksheet_Change(ByVal Target As Range)'

Dim A(1 To 5) As Range

Set A(1) = Range("A1")
Set A(2) = Range("A2")
Set A(3) = Range("A3")
Set A(4) = Range("A4")
Set A(5) = Range("A5")

If Intersect(Range("A1:A5"), Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
    A(1) = A(2) + A(3) + A(4) + A(5)
    A(2) = A(1) - A(3) - A(4) - A(5)
    A(3) = A(1) - A(2) - A(4) - A(5)
    A(4) = A(1) - A(2) - A(3) - A(5)
    A(5) = A(1) - A(2) - A(3) - A(4)
Application.EnableEvents = True
End Sub

标签： excelvbacalculated-field

Answer 1

像这样的东西：

Private Sub Worksheet_Change(ByVal Target As Range) '

    Dim A(1 To 5) As Range

    Set A(1) = Range("A1")
    Set A(2) = Range("A2")
    Set A(3) = Range("A3")
    Set A(4) = Range("A4")
    Set A(5) = Range("A5")

    If Intersect(Range("A1:A5"), Target) Is Nothing Then Exit Sub

    Application.EnableEvents = False
        If Intersect(A(1), Target) Is Nothing Then A(1) = A(2) + A(3) + A(4) + A(5)
        If Intersect(A(2), Target) Is Nothing Then A(2) = A(1) - A(3) - A(4) - A(5)
        If Intersect(A(3), Target) Is Nothing Then A(3) = A(1) - A(2) - A(4) - A(5)
        If Intersect(A(4), Target) Is Nothing Then A(4) = A(1) - A(2) - A(3) - A(5)
        If Intersect(A(5), Target) Is Nothing Then A(5) = A(1) - A(2) - A(3) - A(4)
    Application.EnableEvents = True
End Sub