Accumulating data in one cell

[dealbanker]

Hello

Has anyone had any experience in code to keep a running total of a cell's changing data?

The cells in column AD5:AD35 contain data as a result of calculations that give me a % number for each runner. These % numbers will/may change for each cell in that column every 3 seconds.

I am trying to find a way to have each cell's data in that column to add the data that appears every 3 seconds, into their adjacent cells (AE5:AE35).

That is, the number that appears in a cell in the AD column will add to the adjacent cell in the AE column that has a running/cumulative total figure for the AD cell.

Any ideas will be helpful

Thanks
Dealer

[Captain Sensible]

Use a bit of VBA, try this or just add the code to any existing VBA on your sheet

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub


Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Range("AE5:AE35").Value = Range("AD5:AD35").Value



Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Captain Sensible]

Missed the fact you wanted to accumulate totals maybe try something like this or similar, in the middle of Ascot atm, I'm guessing your refresh is set to 3 seconds otherwise you'd need to add in some timer routine

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object


For Each cell In Target
iRow = cell.Row
iCol = cell.Column



If iCol = 15 And iRow > 4 And iRow < 36 Then


Range("AE" & iRow).Value = Range("Y" & iRow).Value + Range("AE" & iRow).Value


Application.EnableEvents = True
End If



Next cell

End Sub

[Captain Sensible]

Ooops pasted the wrong code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object


For Each cell In Target
iRow = cell.Row
iCol = cell.Column



If iCol = 15 And iRow > 4 And iRow < 36 Then
If IsEmpty(Range("AE" & iRow).Value) Then Range("AE" & iRow).Value = 0
If IsEmpty(Range("AD" & iRow).Value) Then Range("AD" & iRow).Value = 0

Range("AE" & iRow).Value = Range("AD" & iRow).Value + Range("AE" & iRow).Value


Application.EnableEvents = True
End If



Next cell

End Sub

[dealbanker]

Thanks Captain

Sorry, yes GRUSS is set to 3 sec refresh.

I pasted this code in but can not log to excel from GRUSS since I did it. It opens the workbook but does not allow me to open a worksheet, just blank.

I already have a time change based code in this worksheet that you were good enough to help with last year. Is it possible that they are conflicting, both being time change codes?

regards
Dealer

[Captain Sensible]

Possibly, if you already have a Worksheet_Change routine you'd just need to add the coding into the existing one.

[dealbanker]

Got it and done, thanks Captain
Working a treat