VBA动态单元格触发器统计值未按预期更新问题

问题说明 #

你编写的这段VBA触发器代码目标是为工作表指定区域的单元格绑定修改事件，根据单元格取值统计对应选项的计数，最终将整行的统计结果输出到对应行的O列。

原代码问题根因 #

你当前的代码仅对单元格修改后的新值做了计数累加，既没有对修改前的旧值做对应计数扣减，也没有在每次触发事件时重新统计整行所有单元格的取值，所以修改单元格取值后，旧值的计数会一直保留，就会出现你遇到的Yes和No同时计数的问题。

修复方案 #

推荐使用「每次触发时重置计数器、重新统计整行数据」的方案，逻辑简单稳定，不容易出现计数偏差，修改后的代码如下：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim j As Integer, i As Integer
    Dim Yes_4 As Integer, No_4 As Integer, Not_Applicable_4 As Integer
    Dim Green_4 As Integer, Orange_4 As Integer, Red_4 As Integer
    
    ' 仅处理指定范围内的单元格修改：1-3行，1-4列
    If Target.Row < 1 Or Target.Row > 3 Or Target.Column < 1 Or Target.Column > 4 Then
        Exit Sub
    End If
    
    j = Target.Row
    ' 重置当前行的所有计数器
    Yes_4 = 0: No_4 = 0: Not_Applicable_4 = 0
    Green_4 = 0: Orange_4 = 0: Red_4 = 0
    
    ' 遍历当前行所有需要统计的单元格，重新统计计数
    For i = 1 To 4
        Select Case Cells(j, i).Value
            Case "Yes": Yes_4 = Yes_4 + 1
            Case "No": No_4 = No_4 + 1
            Case "Not applicable": Not_Applicable_4 = Not_Applicable_4 + 1
            Case "Green - Sufficient": Green_4 = Green_4 + 1
            Case "Orange - Largely sufficient with points for follow-up": Orange_4 = Orange_4 + 1
            Case "Red - Insufficient": Red_4 = Red_4 + 1
        End Select
    Next i
    
    ' 输出统计结果到当前行的O列
    Range("O" & j).Value = "The ratings you selected are as follows:" & vbNewLine & _
        "Yes: " & Str(Yes_4) & vbNewLine & _
        "No: " & Str(No_4) & vbNewLine & _
        "Green: " & Str(Green_4) & vbNewLine & _
        "Orange: " & Str(Orange_4) & vbNewLine & _
        "Red: " & Str(Red_4) & vbNewLine & _
        "No Applicable: " & Str(Not_Applicable_4)
End Sub

优化说明 #

• 增加了范围校验，不在统计范围内的单元格修改不会触发统计逻辑，提升运行效率
• 用Select Case替代多层If ElseIf，代码可读性更高
• 每次统计前都重置计数器，重新遍历整行统计，彻底避免旧值计数残留的问题

内容的提问来源于stack exchange，提问作者Abraham Foto