To try to explain this as easy as I can and believe that's not easy. To start I put 2 part of my worksheet (samples 10-12) up because of the size. But starting with sample 10 in the chart starting at By6 you can see the daily downloaded numbers (results) from the race the night before. No I'm not a handicapper just a retired man who like playing the Greyhound every other weekend. as you look across the chart you see date, grade, distance, race #, kennel#, Results T4 (Top 4) best speed T4 etc. This chart just give me a idea of what to look for in each grade and distance of each race.
So after downloading new number daily, I will copy those numbers from another worksheet in my workbook and paste special (value) to the chart in sample 10. The numbers in cells BY6 THRU DP20 will automatically go to the chart starting with GU3 (which is in sample 12 now).
I'm trying to build a 30 day calculation for the numbers in the assign cells starting with GU3 THRU JJ181. after I figure out how to write a code allowing me to keep a on going 30 day total. Sorry the upload size will not let me put my entire worksheet and I hope this does not confuse things to much , keep in mind both samples 10 and 12 come from one worksheet in my 6 sheet workbook.
If you look at race 1 BR6 shows it as a D Grade race and BS6 shows the distance is 550.The results starting at BY6 to CB6 Shows the race came in 2-6-1-5.
Now here what I'm trying to do. Since the First race on 01-09-14E was a D Grade race, go to IE3 in the sample 12 chart. There you see the post/result section for the Grade D races. On 1-09-14E their were 5 D races run and you see that in IE3 to IL6 the results from the 5 races, for Win, Place, Show and 4th. The results show what Box the Greyhounds were running in from 1 to 8. So after 30 days of keep records I can look at a Grade D race before I place a bet and see if the Greyhound I like is running out of a hot or cold box. This 30 day calculation will be kept for all Grades and Distance
starting at section GU3.
Also note (Very Important) regardless what the numbers are in BY6 to DP20 (sample 10) there value when going to there assigned cell in the cells starting at GU3 (sample 12) will always be 1 (one).
As you can see in (sample 12) the Grade D 550 race in cells (IE3 to IL6) that after 5
Grade D 550 races The Greyhound in Box 6 won 2 races on 1-09-14E and the Greyhound running out of the 8 Box Placed 2 time in those 5 races.
So I'm trying to keep my own data for a on going 30 day total to see how may times Boxes 1 thru 8 finished in the Top 4 (Money) and the total. Did the 6 box have 11 wins in the last 30 days, how many times did the 4 box come in 2nd (place) after 30 races and so forth. Same thing done for best speed, average speed, early speed etc. keep a on going 30 day total (record) for each section.
The chart starting at GU3 is suppose to keep adding a on going 30 day total for each cell in each section.
There is a Formula used to separate the Grades and Distance. This one is for (IE3) Grade D 550 Post/Box Results. This formula is different for each cell (Box). But if you look at the Grade A post/box results in cell GU3 you will see the formula for those cell(Box) 1 thru 8. This is done on my entire worksheet for each cell (Box) Grade and Distance.
Below the first code shows The Formula used to separate the Grades and Distance. The second code shows how to do a step by step for writing a on going calculator. But that just from one cell to another. The Last code is the one I'm trying to re-write it shows the target and range BY6:DP20 come where I copy and paste special (value) from another worksheet in my workbook, and it show the targets and range I want the on going 30 day calculator (totals) to go.
But the last code writing is totally wrong because it does no adding for a 2 day total much less a 30 day total.How can I write a on going 30 day calculation using the targets and ranges in code 2 to make what I'm trying to do work. HELP.
Again sorry I had to upload 2 sample to make this even more confusing but due to the size of my worksheet I could not. When you guys retire pick another hobbies. Between trying to learn enough about excel and trying to pick the dogs is about to kill me, lol, just kidding, but both are a challenge, and hopefully I will stay a round a few more years.
Thanks for your reply back. I hope this help explains what I'm trying to do. anymore questions please asked.
Code:
=COUNTIFS($CC$6:$CC$20,1,$BR$6:$BR$20,"D",$BS$6:$BS$20,550)
=COUNTIFS($CD$6:$CD$20,1,$BR$6:$BR$20,"D",$BS$6:$BS$20,550)
=COUNTIFS($CE$6:$CE$20,1,$BR$6:$BR$20,"D",$BS$6:$BS$20,550)
=COUNTIFS($CF$6:$CF$20,1,$BR$6:$BR$20,"D",$BS$6:$BS$20,550)
Code:
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("GU3").Value = Range("GU3").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Code:
'//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("HB218").Value + 1
Range("HG3").Value = Range("HN218").Value + 1
Range("HS3").Value = Range("HZ218").Value + 1
Range("IE3").Value = Range("IL181").Value + 1
Range("IQ3").Value = Range("IX181").Value + 1
Range("JC3").Value = Range("JJ181").Value + 1
Application.EnableEvents = True
End If
End With
End If
End Sub
Reply With Quote
Bookmarks