Excel Addition Formula Question

Posted by Johnny Noskill on June 17, 2001 10:23 PM

Hey All,

I have a problem, and i'll try to describe it as best as I can.

I'm trying to get a cell to do a repeated addition of numbers. Say for example cell A1 has an initial value of 50. I would like a formula/macro that if I typed in another number over top of it, it would add it to itself. So if I typed in 75 over the previous 50 it would then add up to 125.

Is this possible? Any help would be greatly appreciated. Also I'm trying to do all of this in a single cell if possible, but if not that would be okay to.

Thanks,

Posted by Ivan F Moala on June 18, 2001 2:20 AM

Hi Johnny
Oe way to do this is via the Static statement &
the Applications Events.

In the Workbook object place this code;

Private Sub Workbook_Open()
'To Fire the Applications Events we need
'to run this so that it fires the sheets
'events upon openning....then we can initialise
'the Static value.
Sheet1.Range("A1") = Range("A1") + 0
End Sub

In the sheets code (assumnng sheet1) place this event code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Hold the value
Static oldcellval

'Cell to monitor
Dim WatchRg As Range

'Set it here
Set WatchRg = Range("A1")

If Target.Address = WatchRg.Address Then
'Is it a number ?
Application.EnableEvents = False
If Not IsNumeric(Target) Then
'No so UNDO & Exit
Application.Undo
GoTo Fin
Else
Target = Target + oldcellval
oldcellval = Target
End If
End If
Fin:
Application.EnableEvents = True
End Sub

So when you enter data into A1 it fires the sheet
change event and runs this macro.
Note the workbook open event with the adding of
A1 + 0 is required to initialise the Static Value

HTH

Ivan