summing up a cell value

[abdulla saeed]

please let me know the code to add the value that i dynamically enter to a cell to another cell without changing the next cells summed value.


it is that im using two cells. that is A1 and A2. im changing the value of A2 daily but at the same time i want to add this changing value to A1. the sum formula adds the value to A1 when i enter a value to A2 but at the same time it also deletes that figure when i delete the figure in A2. i want the added figure in A1 to be permanent neverthless i delete the figure in A2.



please help me with this

[Whizbang]

Paste this code into your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("A1") = Range("A1") + Range("A2")
    End If
End Sub

[DonkeyOte]

To prevent the Change event being needlessly invoked a second time (ie when A1 is altered) toggle Events - you might also want to use Val function to avoid possible coercion errors.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target,Range("A2")) Is Nothing Then
        Application.EnableEvents = False
        Range("A1") = Val(Range("A1")) + Val(Range("A2"))
        Application.EnableEvents = True
    End If
End Sub

(you could also add an IsNumeric test if you wanted to prevent numeric alpha strings being counted - eg 1Apple would add 1 to result in above form)

P.S. Are whizbang and I now entitled to free holidays in the Maldives ?

[Whizbang]

Ah. That makes sense, DonkeyOte. I never thought to turn off events to prevent needless processing. I guess I couldn't see the forest for all the trees.

[abdulla saeed]

Ah. That makes sense, DonkeyOte. I never thought to turn off events to prevent needless processing. I guess I couldn't see the forest for all the trees.

anyways thank you guys. it really solves my problem