Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Accumulate a value in one cell with data from another cell

0

Hello All

     I am very new to Excel and this may be a silly question, but I want to create an accumulator in a cell (ex. D4) that adds to itself from input from another cell (ex. A1).  So, when A1=10, D4=10.  Then A1 =15, D4=25.  A1=3, D4=28.  I also want this value to remain in the workbook after saving and closing.  I will have two of these for two different values that are not related, but accept input from the same source cell.  To complicate matters, I want one of these accumulators to accept negative numbers, but not the other.  I believe this can be solved with a simple If statement using < 0 to hedge out anything negative form that specific accumulator.  Any help would be greatly appreciated.  Thanks

Answer
Discuss

Answers

0
Selected Answer

The idea you describe can't be created using workshet formulas. You will need VBA. The code below must be installed in the code sheet of the worksheet on which you want the action.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 04 Jun 2018
    
    Const OutPutCell As String = "D4"
    ' input cell addresses prefixed with a plus sign (+)
    ' disregard negative entries:-
    Const InputCells As String = "A1,+A3,B3,+C3"
    
    Dim Tgts() As String, PlusOnly() As Boolean
    Dim Accu As Single
    Dim i As Integer
    
    Application.EnableEvents = False
    With Target
        ' changing the OutputCell clears existing total
        If .Address = Range(OutPutCell).Address Then
            .Value = vbNullString
        End If
    End With
    Tgts = Split(InputCells, ",")
    ReDim PlusOnly(UBound(Tgts))
    For i = 0 To UBound(Tgts)
        PlusOnly(i) = CBool(InStr(Tgts(i), "+"))
        Tgts(i) = Trim(Replace(Tgts(i), "+", ""))
    Next i
    For i = 0 To UBound(Tgts)
        With Target
            If .Address = Range(Tgts(i)).Address Then
                Accu = Val(.Value)          ' read the entered value
                If (Not PlusOnly(i)) Or (Accu > 0) Then
                    Range(OutPutCell).Value = Range(OutPutCell).Value + Accu
                End If
                Exit For
            End If
        End With
    Next i
    Application.EnableEvents = True
End Sub

You will have to adapt the addresses of InputCells and OutputCell to your own worksheet. Please also take note of the comments I have added to the code. I also attach a workbook with an implementation of the code.

Discuss

Discussion

Wow, thanks.  I appreciate the amount of effort you put into your answer.  Very helpful.  Works great.
Stasis24 (rep: 2) Jun 4, '18 at 7:45 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login