实现E2:E709值变更时F2:F709同步计数的VBA代码改造需求

Hey there! Let's tweak your existing Worksheet_Change code to work with the E2:E709 range and update counts in F2:F709. The original code only tracks a single cell's old value—we need a way to track old values for every cell in your target range independently. Here's the revised code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Use a dictionary to store old values for each cell in E2:E709
    Static OldValues As Object
    Dim TargetCell As Range
    Dim CellKey As String
    
    ' Initialize the dictionary if it doesn't exist yet
    If OldValues Is Nothing Then
        Set OldValues = CreateObject("Scripting.Dictionary")
    End If
    
    ' Check if the changed cells intersect with our target range E2:E709
    Dim TargetRange As Range
    Set TargetRange = Me.Range("E2:E709")
    Set TargetCell = Intersect(Target, TargetRange)
    
    If Not TargetCell Is Nothing Then
        Application.EnableEvents = False ' Prevent infinite loop from the change we're about to make
        
        ' Loop through each changed cell in the target range (handles multi-cell changes like paste)
        For Each TargetCell In TargetCell
            CellKey = TargetCell.Address(False, False)
            
            ' If we don't have a stored old value for this cell, save the current value first
            If Not OldValues.Exists(CellKey) Then
                OldValues(CellKey) = TargetCell.Value
            End If
            
            ' Compare current value to stored old value
            If TargetCell.Value <> OldValues(CellKey) Then
                ' Update the count cell (offset 1 column to the right, which is column F)
                TargetCell.Offset(, 1).Value = TargetCell.Offset(, 1).Value + 1
                ' Update the stored old value to the new value
                OldValues(CellKey) = TargetCell.Value
            End If
        Next TargetCell
        
        Application.EnableEvents = True ' Re-enable events
    End If
End Sub

Key Changes Explained: #

• Static Dictionary: We use a Scripting.Dictionary stored as a static variable to keep track of the old value for every cell in E2:E709. This persists between code runs so we don't lose track of previous values.
• Range Check: The Intersect function ensures we only process changes that happen within E2:E709—ignores any other cell changes on the worksheet.
• Multi-Cell Handling: We loop through each cell in the changed range, which works if someone pastes values into multiple cells at once.
• Initialization: We check if the dictionary exists when the code runs, and create it if it's the first time.

Important Notes: #

• Make sure you paste this code into the worksheet module (not a standard module) where your E2:E709 range lives.
• If you ever need to reset all counts or clear the stored old values, you can add a small macro to set OldValues = Nothing and reset F2:F709 to 0.

内容的提问来源于stack exchange，提问作者Fahad Nasif