Loop through section, then sum.

[kgall89]

Hello all,

Attached is a sample workbook.

I have a list of games and win predictions. If the game is picked correctly, a 1 will pop in Q if it is picked incorrectly a 1 will pop up in R.

I am trying to create a loop that will sum the Q an R column for each specific day. As you can see in the workbook, it starts on a Thursday and then has games on Friday as well. I would like to sum the Thursday W and L and place than in the appropriate cell. Each worksheet will have a different appropriate cell. On sheet1 I need Thursdays games to tally in H16 and Fridays into H27 but on sheet2 they will need to tally in H6 and H10. It is all dependent on how many games played that day.

I thought a loop that would search Column A containing a "Thursday" then go down until it finds a "Friday", add all the Q and R in that range and place it into the appropriate cell in H.

Maybe I'm making this more complicated than it needs to be, but any help is appreciated.

Thank you,

Kyle

[Richard Buttrey]

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

If you're up to changing your data so that you record each game and its prediction and result on a single row then maybe we can advise further.

[kgall89]

Thank you for the reply Richard.

As of now I believe I do record each game prediction and result in the same row.

Attachment 637984

I made a mistake by not transferring the column headers into my sample, I apologize. For this row: The game is - A is Team 1, E is Team 2. The prediction - G. F is the actual winner, H is the actual loser. The result - If the prediction is correct, Q =1 if not R=1


I think the issue I am having is not being able to fully explain my situation with proper VBA and excel terms as I am still learning. I am up for changing my collection method but I feel this is an appropriate for my use.

Thank you!

[kgall89]

Okay I have made some progress. But its pretty ugly.

I have been able to calculate the records for each day but as I continue the actual math equation will get pretty long and im wondering if there is a cleaner way to do this. Below is the code and attached is the updated workbook. Some weeks will have games Tuesday to Saturday.

Please Login or Register  to view this content.

This potion has me thinking for now:

Please Login or Register  to view this content.

Something like

For range i:j sum q range

instead of the (w2 - w) & "-" & (l2 - l) because this will end up being w5-w4-w3-w3-w2-w and l5-l4-l3-l2-l and the percentage will be very ugly


Thanks for any suggestions!

Kyle

[Richard Buttrey]

As you wondered in your original I agree that you are overcomplicating this. You really shouldn't need to use VBA for what is essentially a straighforward sum of records that meet a certain criteria, i.e. the same date.

Your difficulty stems from the fact that the indvidual rows don't carry a date identifier. If you make a simple change and add a new column A which contains a date for each row then a simple SUMIF function is all you need.
And rather than have a daily total underneath a series of rows have a separate sheet which contains all the dates and a SUMIFS() function