On Going Calculation

[justme1052]

I’m trying to write a code to do whole a sheet with , On Going Calculation . I have uploaded a worksheet sample. it is easier for me to show than me trying to explain. my trying to explain gets everyone confused .

I also posted this on Excel.com Forum, a couple days ago but never receive a reply back.

I was wanting a sheet to sheet, but I have changed my spread sheet and put everything on the same sheet
I tried writing a code for each column from BR6 to DP20 to go to each section Cell and column starting at GU3 . One of the members at excel.com a while back show me how to write a on- going calculation to go To one cell , which is what I had asked for and it worked. But trying to write it for more than one cell, not only did my attempt to write code not work, it clear all the value figures in the entire section. So I exited out and clicked do not save. So I need the experts’ advice again.
Below was my attempt at writing a code. I put the Value +1 in it so would just add 1 to the the cell it was going to and not the box number that won the race.
Example on sample sheet the number (value) in BY7 is 5 and it would go to sample B sheet 2 AK3 as 1. All the numbers in sheet 1 from BY7 to DP20 are just box numbers, my goal is to see who many times each box comes in.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "BY6:DP20" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("GU3").Value = Range("HB3").Value + 1
Range("GB4").Value = Range("HB4").Value + 1
Range("GB5").Value = Range("HB5").Value + 1
Range("GB6").Value = Range("HB6").Value + 1
Range("GB12").Value = Range("HB12").Value + 1
Range("GB13").Value = Range("HB13").Value + 1
Range("GB14").Value = Range("HB14").Value + 1
Range("GB15").Value = Range("HB15").Value + 1
Application.EnableEvents = True
End If
End If
End With
End Sub
I only tried 2 sections but the codes did not work. I would like to know how to write a proper code starting at GU3, so it will send the correct value to each cell . Grade A will go to Grade A info section Grade B to Grade B ,etc.
Also is there a way to make this a 30 day results so after 30 days when it add day 31 it will go back to the first day so it becomes a continues 30 days results.

Due to size only part of sample 1 is shown. but BY6 to DP20 is where the results number appear after I download the results each day.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Below is a copy of the help I received from the excel.com member. his help worked for what I asked for, he did a great job. I was hoping after I saw how to get started I
could go from there and complete the whole sheet on my own. Sorry to say that did not happen.

The title of my Thread on excelforum.com is Sheet to Sheet On Going calculation .
----------------------------------------------------------------------------------------
ongoing calculator Formula

Here are the step by step instructions on using the VBA event macro. This is your best option for the task at hand.

Cell A1 is where the new entry is made and cell B8 is the running total.

In Excel 2007 (as your profile indicates)...

Open the file where this is to be used
Select the sheet where this is to be used
Right click the sheet tab
Select: View Code (this will open the VB editor)
Copy the code below and paste it into the window that opens on the right hand side of the screen:

[Select Code] copy to clipboard


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "BY6" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("BG").Value = Range("BG7").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
Close the VB editor to return to Excel

Try it out. Enter some numbers in cell A1 and observe cell B8.

If you want to reset the total just delete the contents of cell B8.

You'll have to save the file as a macro enabled file in the *.xlsm file format.
-----------------------------------------------------------------------------------------------------------

one page sample


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "AP1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("AN1").Value = Range("AN1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

[Admin]

Hi

The question is not clear.


...I tried writing a code for each column from BR6 to DP20 to go to each section Cell and column starting at GU3 . One of the members at excel.com a while back show me how to write a on- going calculation to go To one cell , which is what I had asked for and it worked. But trying to write it for more than one cell...

may be..


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'//if the target range is in anywhere on range "BY6:DP20"
If Not Intersect(Target, Range("BY6:DP20")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("GU3").Value = Range("HB3").Value + 1
Range("GB4").Value = Range("HB4").Value + 1
Range("GB5").Value = Range("HB5").Value + 1
Range("GB6").Value = Range("HB6").Value + 1
Range("GB12").Value = Range("HB12").Value + 1
Range("GB13").Value = Range("HB13").Value + 1
Range("GB14").Value = Range("HB14").Value + 1
Range("GB15").Value = Range("HB15").Value + 1
Application.EnableEvents = True
End If
End With
End If

End Sub

[justme1052]

Thanks for your reply. What I'm trying to do is to build and keep a 30 day on going calculation for cells BY6 to DP20.
I posted just 2 of the 130 sections it will take to complete my project. I will put in the rest once I, well you figure out
what I need to do to make this work.
The only thing I change in your code was the letters.
You started with Range("GU3").Value = Range("HB3").Value + 1 which is correct. But from there you put
Range("GB4").Value = Range("HB4").Value + 1. I'm hoping using GB4 to GB15 was a typo. So I changed them all back to GU. The cell value numbers in cells BY6 to DP20 change daily as I download the results.

I put your code in this way, I clicked BY6 , click Developer and clicked view code. then I copied and pasted your code.
Each section and each cell starting with GU3 is identified by Grade and Distance (A, B,C,D,M) (550, 660, 770)
=COUNTIFS(BY$6:BY$20,1,$BR$6:$BR$20,"A",$BS$6:$BS$20,550)
showing that GU3 is looking for any race that is Grade A and running distance of 550 coming from cells BY6 thru DP20.

This part works great with all the results from BY6 thru DP20 going to the right section and cell.

After putting in your code I went back 5 races but each time all I get starting with GU thru HB3 and through out to GU15 to HB15 were the value in BY6 thru DP20, were the downloaded valve that day.

The cells starting with GU3 were not keeping a on going total. I was hoping using these codes would keep a on going calculation totals would work.
Did I put the code in wrong because I get this error each time I download the results.
------------------------------------------------------------------------------------------------------------------
Block If without End If
An error occurred due to an incomplete statement. This error has the following cause and solution:

##An If statement is used without a corresponding End If statement.
A multiline If statement must terminate with a matching End If statement. For nested If...End If statements, make sure

there is a correctly matched If...End If structure inside each enclosing If...End If structure.
------------------------------------------------------------------------------------------------------------------
For some reason the numbers do not add up. They just give the downloaded value put in BY6 thru DP20 that day.
Is there something else I need to do or another way to do it.

Being retired I have a lot of time on my hands and keeping up with Greyhounds and trying to learn enough about
excel to make it easier for me to see how the dogs are doing is how I spend a lot of it.
But when I put the results in BY6 thru DP20, my total sections starting with GU3 are supposed to show me what grades and distance and how many times the speed, early speed, late speed etc. you know how many times the top speed dogs finished first and how many time it was 8th in the last 30 days, and same for the rest.

I hope this help explain what I'm trying to do and what the purpose of both charts are for. one the results, two the
totals. if this format is not set right, hopefully you or someone can help me figure out how to do this.
Thanks