CPerry
09-10-2018, 03:49 AM
22854
I already have a sub routine which moves numbers down a range once a second from the top, in Exp 1 this would be Cells B5:E5. In example 1 you can see that there are no changes so at the bottom of the range I simply add up all of the red numbers. Same with the yellow, green and blue. The problem comes when these ranks change. (The colours are just for this example, they aren’t required or wanted for the final system as they will slow the code down)
The names at the top (Alpha, Bravo, Charlie and Delta) are returns of formulas in my actual system which appear in rank order. In example 2 you can see that 30 seconds ago up to 10 seconds ago Alpha was first but is now second so at the bottom of the range I sum all of the red numbers but this time they cross 2 columns. In example 3 Alpha drops from 1stto 3rdso the red numbers are across 3 columns.
My problem comes when trying to sum the numbers at the end. In example 2, I don’t just want to sum the entire second column because the bottom 20 numbers don't belong to Alpha, so the ranges get split; do you know what I mean? Can anybody help add to my existing code please to help me when cells in the 2ndrow change order?
Here is how the ranges ‘tick’ at the moment (note the first row is B40:K70 not B5:E5 in the actual system):
Sub Logger ()
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Range(“B38”).Value <> Range(“B1”).Value Then Range(“B41:L71”).ClearContents: Range(“B1”).Value
If Range(“B39”).Value = “” Then Range(“B39”).Value = Time () – TimeValue(“00:00:01”)
If Time () >= TimeValue(Range(“B39”).Text) + TmeValue(“00:00:01”) Then
Range(“B41:K71”).Value = Range(“B40:K70”).Value
Range(“B39”).Value = Time ()
End if
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
I already have a sub routine which moves numbers down a range once a second from the top, in Exp 1 this would be Cells B5:E5. In example 1 you can see that there are no changes so at the bottom of the range I simply add up all of the red numbers. Same with the yellow, green and blue. The problem comes when these ranks change. (The colours are just for this example, they aren’t required or wanted for the final system as they will slow the code down)
The names at the top (Alpha, Bravo, Charlie and Delta) are returns of formulas in my actual system which appear in rank order. In example 2 you can see that 30 seconds ago up to 10 seconds ago Alpha was first but is now second so at the bottom of the range I sum all of the red numbers but this time they cross 2 columns. In example 3 Alpha drops from 1stto 3rdso the red numbers are across 3 columns.
My problem comes when trying to sum the numbers at the end. In example 2, I don’t just want to sum the entire second column because the bottom 20 numbers don't belong to Alpha, so the ranges get split; do you know what I mean? Can anybody help add to my existing code please to help me when cells in the 2ndrow change order?
Here is how the ranges ‘tick’ at the moment (note the first row is B40:K70 not B5:E5 in the actual system):
Sub Logger ()
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Range(“B38”).Value <> Range(“B1”).Value Then Range(“B41:L71”).ClearContents: Range(“B1”).Value
If Range(“B39”).Value = “” Then Range(“B39”).Value = Time () – TimeValue(“00:00:01”)
If Time () >= TimeValue(Range(“B39”).Text) + TmeValue(“00:00:01”) Then
Range(“B41:K71”).Value = Range(“B40:K70”).Value
Range(“B39”).Value = Time ()
End if
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub