Hi Cperry
I have written some code to show you how this should work.
I am using 4 columns A to D , "Amount" "Time Added" , "Fraction" and "Current Value"
I have allowed for a buffer with a maximum of 30 values but it can be any number you like.
there are two subroutines
"AddAmount" this add an amount ( this code using cell J1 as the source of the amount) to the buffer and records the time and the fractional amount to be decremented
"Decrement" is the routine that will run every second and will keep the current value updated and decremented over 30 sec. 30 seconds after an amount is added this routine will delete the row making it available for the next amount which is added.
It is all very simple code so should be self explanatory
Sub Decrement()
inarr = Range(Cells(1, 1), Cells(30, 4))
timenow = Time()
sec30 = 1 / (24 * 60 * 2)
For i = 2 To 30
' Check time
timediff = Abs(timenow - inarr(i, 2))
If timediff > sec30 Then
For k = 1 To 4
inarr(i, k) = ""
Next k
Else
' decrement
If inarr(i, 4) = "" Then
' first iteration
inarr(i, 4) = inarr(i, 1) - inarr(i, 3)
Else
inarr(i, 4) = inarr(i, 4) - inarr(i, 3)
End If
End If
Next i
Range(Cells(1, 1), Cells(30, 4)) = inarr
End Sub
Sub addamount()
inarr = Range(Cells(1, 1), Cells(30, 3))
For i = 2 To 30
If inarr(i, 1) = "" Then
' this is a blank row so populate it from j1
inarr(i, 1) = Cells(1, 10)
inarr(i, 2) = Time()
inarr(i, 3) = inarr(i, 1) / 30
Exit For
End If
Next i
Range(Cells(1, 1), Cells(30, 3)) = inarr
End Sub