After posting this problem on both excelforum.com and excelfox.com last month and getting a couple of replies asking
different questions and offering help, but no one came up with a answer.
So I am posting this again hoping this time round someone can see and understands what I'm trying to do.
Below is the code I have written. I'm trying to create, build and keep a 30 day on going value total for cells
listed in the code below.
Cells BY6:DP20 gets new downloaded numbers daily and the results automatically go to their assigns cells GU3
thru JJ181. But that's all their doing with this written code. the values show only the new download number
value and does not do a on going total from the day(s) before. Hopefully someone can help me with errors in
the code and put me on the right track to building and keeping a ongoing 30 day total.
I started my code in this way, I clicked BY6 , then click Developer and clicked view code. then I put in the
code you see below.
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
a Grade A to Grade M and running distance of 550 to 770, from cells BY6 thru DP20 in 15 races each night.
This part works great with all the results from BY6 thru DP20 going to the right section and cell.
Regardless the value of the numbers downloaded to BY6 thru DP20, the values going to GU3 thru JJ181,
will always add just 1 to the cells getting numbers that day. So if BY6 downloaded number is 7 and it is
going to GZ3, I only want its value to be a 1, not a 7 coming from BY6. then add 1 to go to what ever the
value in cell GZ3. If GZ3 has a total of 4 before the new total will be 5.
For some reason (code not completed or done right) the numbers do not add up for 2 days much less trying to build and maintain a 30 day on going total.
Instead I just get the downloaded value put in BY6 thru DP20 that day. So GU3 THRU JJ181 are not giving
me a on going total.Is there something else I need to add to the code to make it work or is there another way to do this.
Being retired I have a lot of time on my hands. I try keeping up with Greyhounds. So I'm trying to learn
enough about excel to make it easier for me to see how the dogs are doing daily. I'm just keeping my own data sheet. well trying to.
So when I put the downloaded numbers 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. 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 in the sample.
one is for the daily downloaded numbers and the other will show the last 30 day results after I get it build
up to 30 days and each downloaded numbers after 30 days will keep it on a on going 30 day total.
Hopefully someone can help me figure out how to do this.
in book2 sample the downloaded number chart starts at BY6. SCROLL to GU3,that where the on going calculation chart starts.
they only show today's downloaded value and does not do a on going calculation at all.
Please someone help me get this code working, so I can receive a ongoing 30 day total.
Thanks
Here is my code:
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("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("JB3").Value = Range("JJ181").Value + 1
Application.EnableEvents = True
End If
End With
End If
End Sub
Bookmarks