Excel Accumulator Needs Some Tweaking

J

JAMHOME

Joined
Jun 30, 2011
Messages
2
Reaction score
0
How do I alter the code to stop the data being reset upon when reopening the workbook? I open the workbook and cell E13 had a 10, I enter a 5, and it puts in 5 instead of 15. The code works when you initially open the workbook and enter in values but if you close and reopen and make a change it resets.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'turn events back on
Application.EnableEvents = True
Static Accum As Collection
     If Accum Is Nothing Then Set Accum = New Collection
     Const ValidRngAddr As String = "e13:e26"
     Dim ValidRng As Range
     Set ValidRng = Application.Intersect( _
        Target, Target.Worksheet.Range(ValidRngAddr))
     If ValidRng Is Nothing Then Exit Sub
     Dim aCell As Range
     For Each aCell In ValidRng
        Dim Rslt As Double: Rslt = 0
        With aCell
        If Not IsEmpty(.Value) And IsNumeric(.Value) Then
            On Error Resume Next
            Rslt = Accum(.Address)
            On Error GoTo 0
            Rslt = Rslt + .Value
            End If
        On Error Resume Next
        Accum.Remove .Address
        On Error GoTo 0
        Accum.Add Rslt, .Address
        On Error Resume Next
        Application.EnableEvents = False
        .Value = Rslt
        Application.EnableEvents = True
        On Error GoTo 0
            End With
        Next aCell
End Sub
 
Back
Top