VBA To Loop and Calculate scores

[lavster]

I've been spending a silly amount of time trying to get this vba to work (probably more than I could have done it manually over a whole season) and now its bugging me to get it working.

I have each match played on the left, Result, scores
i.e
United v swansea HW 2 1
leicster v everton D 2 2
arsenal v chelsea AW 1 2
etc etc

then next to that each player guesses the match result, score and the total points is calculated below.

Basically its 3 points for getting the result bang on, 2 points for guessing a draw (incorrect score though) and 1 point for guessing an Away win or home win without getting the correct score.

If anyone could point me in the right direction or take a look at the spreadsheet i'd be very grateful.

[pjwhitfield]

Please Login or Register  to view this content.

As your sheet stands you would then calculate using

Please Login or Register  to view this content.

however unless the goals are being used independantly elsewhere then personally, Id have single columns for the scores showing them as 2-1 etc, your formula then becomes:

Please Login or Register  to view this content.

[lavster]

Hi Thanks for the post.
I've created this method now that works great for the one column.
The main problem I have is how to loop through all the results?

So i'd need to loop through the actual results, then loop through the guesses for the players one player at a time?

Can you advise with this?

[pjwhitfield]

Im not sure what you mean? This would be a function that you'd use in a cell in order to pull a single result in

ie place the following in E14 and drag down to E24

Please Login or Register  to view this content.

then copy E14 and paste in H14, K14, N14 etc

et voila all the scores calculated