bowling score help

golf401

New Member
Joined
May 4, 2003
Messages
40
I've seen a few bowling formulas within mr. excel but nothing in code. so i tried it and the only problem i have is when a player strikes.....then the code should add the next two balls thrown..... it works if the next two are strikes but not if there is no 'mark'. So basically its not adding the second ball. The code looks right unless I'm just missing something. heres the code....
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'********** FRAME 1 **********
'strike 1 & 2
If [b3] + [d3] = 20 Then
    [score1] = [b3] + [d3] + [f3]
        Else
            'strike 1
            If [b3] = 10 And [d3] <> 10 Then
                [score1] = [b3] + [d3] + [e3]
            End If
                'spare
                If [b3] + [c3] = 10 Then
                    [score1] = [b3] + [c3] + [d3]
                        Else
                        'no marks
                            [score1] = [b3] + [c3]
                                End If
End If
'******** END FRAME 1 ********

'********** FRAME 2 **********
'strike 2 & 3
If [d3] + [f3] = 20 Then
    [score2] = [score1] + [d3] + [f3] + [h3]
        Else
            'strike 2
            If [d3] = 10 And [f3] <> 10 Then
                [score2] = [score1] + [d3] + [f3] + [g3]
            End If
                'spare
                If [d3] + [e3] = 10 Then
                    [score2] = [score1] + [d3] + [e3] + [f3]
                        Else
                        'no marks
                        [score2] = [score1] + [d3] + [e3]
                            End If
End If
'******** END FRAME 2 ********
'********** FRAME 3 **********
'strike 3 & 4
If [f3] + [h3] = 20 Then
    [score3] = [score2] + [f3] + [h3] + [j3]
        Else
            'strike 3
            If [f3] = 10 And [h3] <> 10 Then
                [score3] = [score2] + [f3] + [h3] + [i3]
            End If
                'spare
                If [f3] + [g3] = 10 Then
                    [score3] = [score2] + [f3] + [g3] + [h3]
                        Else
                        'no marks
                        [score3] = [score2] + [f3] + [g3]
                            End If
End If
'******** END FRAME 3 ********
'********** FRAME 4 **********
'strike 4 & 5
If [h3] + [j3] = 20 Then
    [score4] = [score3] + [h3] + [j3] + [l3]
        Else
            'strike 2
            If [h3] = 10 And [j3] <> 10 Then
                [score4] = [score3] + [h3] + [j3] + [k3]
            End If
                'spare
                If [h3] + [i3] = 10 Then
                    [score4] = [score3] + [h3] + [i3] + [j3]
                        Else
                        'no marks
                        [score4] = [score3] + [h3] + [i3]
                            End If
End If
'******** END FRAME 4 ********
'********** FRAME 5 **********
'strike 5 & 6
If [j3] + [l3] = 20 Then
    [score5] = [score4] + [j3] + [l3] + [n3]
        Else
            'strike 5
            If [j3] = 10 And [l3] <> 10 Then
                [score5] = [score4] + [j3] + [l3] + [m3]
            End If
                'spare
                If [j3] + [k3] = 10 Then
                    [score5] = [score4] + [j3] + [k3] + [l3]
                        Else
                        'no marks
                        [score5] = [score4] + [j3] + [k3]
                            End If
End If
'******** END FRAME 5 ********
'********** FRAME 6 **********
'strike 6 & 7
If [l3] + [n3] = 20 Then
    [score6] = [score5] + [l3] + [n3] + [p3]
        Else
            'strike 6
            If [l3] = 10 And [n3] <> 10 Then
                [score6] = [score5] + [l3] + [n3] + [o3]
            Else
                'spare
                If [l3] + [m3] = 10 Then
                    [score6] = [score5] + [l3] + [m3] + [n3]
                    Else
                        'no marks
                            [score6] = [score5] + [l3] + [m3]
End If
End If
End If




'******** END FRAME 6 ********
'********** FRAME 7 **********
'strike 7 & 8
If [n3] + [p3] = 20 Then
    [score7] = [score6] + [n3] + [p3] + [r3]
        Else
            'strike 7
            If [n3] = 10 And [p3] <> 10 Then
                [score7] = [score6] + [n3] + [p3] + [q3]
            Else
                'spare
                If [n3] + [o3] = 10 Then
                    [score7] = [score6] + [n3] + [o3] + [p3]
                    Else
                        'no marks
                            [score7] = [score6] + [n3] + [o3]
                            End If
                            End If
                            End If
'******** END FRAME 7 ********
'********** FRAME 8 **********
'strike 8 & 9
If [p3] + [r3] = 20 Then
    [score8] = [score7] + [p3] + [r3] + [t3]
        Else
            'strike 8
            If [p3] = 10 And [r3] <> 10 Then
                [score8] = [score7] + [p3] + [r3] + [s3]
            Else
                'spare
                If [p3] + [q3] = 10 Then
                    [score8] = [score7] + [p3] + [q3] + [r3]
                    Else
                        'no marks
                            [score8] = [score7] + [p3] + [q3]
                            End If
                            End If
                            End If
'******** END FRAME 8 ********
'********** FRAME 9 **********
'strike 9 & 10
If [r3] + [t3] = 20 Then
    [score9] = [score8] + [r3] + [t3] + [u3]
        Else
            'strike 9
            If [r3] = 10 And [t3] <> 10 Then
                [score9] = [score8] + [r3] + [s3] + [t3]
            Else
                'spare
                If [r3] + [s3] = 10 Then
                    [score9] = [score8] + [r3] + [s3] + [t3]
                    Else
                        'no marks
                            [score9] = [score8] + [r3] + [s3]
                            End If
                            End If
                            End If
'******** END FRAME 9 ********
'********** FRAME 9 **********
'strike ball one two & three
If [t3] + [u3] + [v3] = 30 Then
    [score10] = [score9] + [t3] + [u3] + [v3]
        Else
            'strike ball one and spare two/three
            If [t3] = 10 And [u3] <> 10 Then
                If [u3] + [v3] = 10 Then
                [score10] = [score9] + [t3] + [u3] + [v3]
                Else
                [score10] = [score9] + [t3] + [u3]
                End If
                End If
                End If
'******** END FRAME 10 ********
End Sub

thanks in advance
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
it might be considered unfair for you to expect us to both inspect the code & deduce the data set up that it's operating on - a little info on the latter would not go amiss :)
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
Hi!
Im not a bowling fanatics. I hope I have understood scoring
bowling.
Please see below. This could be three Posts.
Bowling.xls
ABCDEFGHIJKLMNOP
1Players1stFramescore2ndFramescore3rdFramescore4thFramescore5thFramescore
21x203s366143x73x95
32x30x60x90x120x150
439093517612436338142
549093s2561323s508159
65x203s40x596368x84
76x30x60x90x120x150
87000000000000000
9800000
10900000
111000000
12
13Symbbols
14XStrike
15SSpare
Sheet2
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
Continuation of the above sheet!
Bowling.xls
QRSTUVWXYZAAABACADAEAF
16thFramescore7thFramescore8thFramescore9thFramescore10thFrameScore
2x1152s134901437s163xxx193
3x180x210x240x270xxx300
453502557806571738182
55s690s8780957s1138s8131
633908s110x140x170xxx200
7x180x210x240x270xxx300
80000000000000000
900000
1000000
1100000
12
13
14
Sheet2
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

The Code!




<font face=Courier New><SPAN style="color:#00007F">Type</SPAN> BallStatus
    Strike <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
    Spare <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
    Score <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>
<SPAN style="color:#00007F">Type</SPAN> OneFrame
    Thisround(0 To 1) <SPAN style="color:#00007F">As</SPAN> BallStatus
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> Scoring(<SPAN style="color:#00007F">ByVal</SPAN> PlayerNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>)
<SPAN style="color:#00007F">Dim</SPAN> MyScore(9) <SPAN style="color:#00007F">As</SPAN> OneFrame
<SPAN style="color:#00007F">Dim</SPAN> MyScorePerFrame(9) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> colx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> Bunos <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
colx = 2
<SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(MyScore) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(MyScore)
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UCase(Range(Cells(PlayerNumber + 1, colx), Cells(PlayerNumber + 1, colx)).Value)
    <SPAN style="color:#00007F">Case</SPAN> "X"
        MyScore(i).Thisround(0).Strike = <SPAN style="color:#00007F">True</SPAN>
        MyScore(i).Thisround(0).Spare = <SPAN style="color:#00007F">False</SPAN>
        MyScore(i).Thisround(0).Score = 10
        MyScore(i).Thisround(1).Strike = <SPAN style="color:#00007F">False</SPAN>
        MyScore(i).Thisround(1).Spare = <SPAN style="color:#00007F">False</SPAN>
        MyScore(i).Thisround(1).Score = 0
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
        MyScore(i).Thisround(0).Score = Range(Cells(PlayerNumber + 1, colx), Cells(PlayerNumber + 1, colx)).Value
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">If</SPAN> MyScore(i).Thisround(0).Strike = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UCase(Range(Cells(PlayerNumber + 1, colx + 1), Cells(PlayerNumber + 1, colx + 1)).Value)
        <SPAN style="color:#00007F">Case</SPAN> "S"
            MyScore(i).Thisround(1).Strike = <SPAN style="color:#00007F">False</SPAN>
            MyScore(i).Thisround(1).Spare = <SPAN style="color:#00007F">True</SPAN>
            MyScore(i).Thisround(1).Score = 10 - MyScore(i).Thisround(0).Score
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            MyScore(i).Thisround(1).Strike = <SPAN style="color:#00007F">False</SPAN>
            MyScore(i).Thisround(1).Spare = <SPAN style="color:#00007F">False</SPAN>
            MyScore(i).Thisround(1).Score = Range(Cells(PlayerNumber + 1, colx + 1), Cells(PlayerNumber + 1, colx + 1)).Value
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
colx = colx + 3
<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">If</SPAN> (UCase(Range(Cells(PlayerNumber + 1, colx - 2), Cells(PlayerNumber + 1, colx - 2)).Value) = "X") <SPAN style="color:#00007F">Then</SPAN>
    MyScore(UBound(MyScore)).Thisround(1).Score = 10
    <SPAN style="color:#00007F">If</SPAN> UCase(Range(Cells(PlayerNumber + 1, colx - 1), Cells(PlayerNumber + 1, colx - 1)).Value) = "X" <SPAN style="color:#00007F">Then</SPAN>
        Bunos = 10
    <SPAN style="color:#00007F">Else</SPAN>
        Bunos = (Range(Cells(PlayerNumber + 1, colx - 1), Cells(PlayerNumber + 1, colx - 1)).Value)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">ElseIf</SPAN> UCase(Range(Cells(PlayerNumber + 1, colx - 2), Cells(PlayerNumber + 1, colx - 2)).Value) = "S" <SPAN style="color:#00007F">Then</SPAN>
        
        MyScore(UBound(MyScore)).Thisround(1).Score = 10 - MyScore(UBound(MyScore)).Thisround(0).Score
        
    <SPAN style="color:#00007F">If</SPAN> UCase(Range(Cells(PlayerNumber + 1, colx - 1), Cells(PlayerNumber + 1, colx - 1)).Value) = "X" <SPAN style="color:#00007F">Then</SPAN>
        Bunos = 10
    <SPAN style="color:#00007F">Else</SPAN>
        Bunos = (Range(Cells(PlayerNumber + 1, colx - 1), Cells(PlayerNumber + 1, colx - 1)).Value)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
<SPAN style="color:#00007F">Else</SPAN>
    MyScore(UBound(MyScore)).Thisround(1).Score = Range(Cells(PlayerNumber + 1, colx - 2), Cells(PlayerNumber + 1, colx - 2)).Value
    Bunos = 0
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(MyScore) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(MyScore) - 1
            <SPAN style="color:#00007F">If</SPAN> MyScore(i).Thisround(0).Strike = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">If</SPAN> MyScore(i + 1).Thisround(0).Strike <SPAN style="color:#00007F">Then</SPAN>
                    <SPAN style="color:#00007F">If</SPAN> i = <SPAN style="color:#00007F">UBound</SPAN>(MyScore) - 1 <SPAN style="color:#00007F">Then</SPAN>
                        MyScorePerFrame(i) = MyScore(i).Thisround(0).Score + MyScore(i + 1).Thisround(0).Score + MyScore(i + 1).Thisround(1).Score
                    <SPAN style="color:#00007F">Else</SPAN>
                        MyScorePerFrame(i) = MyScore(i).Thisround(0).Score + MyScore(i + 1).Thisround(0).Score + MyScore(i + 2).Thisround(0).Score
                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                <SPAN style="color:#00007F">Else</SPAN>
                    MyScorePerFrame(i) = MyScore(i).Thisround(0).Score + MyScore(i + 1).Thisround(0).Score + MyScore(i + 1).Thisround(1).Score
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                            
            <SPAN style="color:#00007F">ElseIf</SPAN> MyScore(i).Thisround(1).Spare = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
                MyScorePerFrame(i) = MyScore(i).Thisround(0).Score + MyScore(i).Thisround(1).Score + MyScore(i + 1).Thisround(0).Score
            <SPAN style="color:#00007F">ElseIf</SPAN> (MyScore(i).Thisround(0).Strike = <SPAN style="color:#00007F">False</SPAN>) And (MyScore(i).Thisround(1).Spare = <SPAN style="color:#00007F">False</SPAN>) <SPAN style="color:#00007F">Then</SPAN>
                MyScorePerFrame(i) = MyScore(i).Thisround(0).Score + MyScore(i).Thisround(1).Score
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i
MyScorePerFrame(UBound(MyScore)) = MyScore(UBound(MyScore)).Thisround(0).Score + MyScore(<SPAN style="color:#00007F">UBound</SPAN>(MyScore)).Thisround(1).Score + Bunos
tmp = 0
colx = 4
<SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(MyScore) <SPAN style="color:#00007F">To</SPAN> UBound(MyScore) - 1
    tmp = MyScorePerFrame(i) + tmp
    Range(Cells(PlayerNumber + 1, colx), Cells(PlayerNumber + 1, colx)).Value = tmp
    colx = colx + 3
<SPAN style="color:#00007F">Next</SPAN> i
  Range(Cells(PlayerNumber + 1, colx + 1), Cells(PlayerNumber + 1, colx + 1)).Value = tmp + MyScorePerFrame(UBound(MyScore))
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>




<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> en
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> isect = Application.Intersect(Target, Range("b2:AE11"))
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> isect <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
PlayerNumber = Target.Row - 1
<SPAN style="color:#00007F">Call</SPAN> Scoring(PlayerNumber)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
en:
<SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
MsgBox "Error occurs" & Chr(13) & Err.Number
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

golf401

New Member
Joined
May 4, 2003
Messages
40
The OS is Windows XP ......and its in xl 2002, to eliminate some work....... frames 2-9 are exact copies besides the cell locations.
Sorry Sixth Sense....... i couldnt get your example to work but the scoring did look pretty accurate.
Anything else you need just ask..... thanks in advance.
bowlingscoresheet.xls
ABCDEFGHIJKLMNOPQRSTUVWXY
1FrameFrameFrameFrameFrameFrameFrameFrameFrameFrame
2Players12345678910
3M101073918255108291101010
42744638196116136155175205
5
6
7
8
9
10
11
12
13
14
15
16
17
Sheet1
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

Hi!
where do you want to see the scores? as my sheet, I have it right after each frame.If you set it up like mine, it should work.
pm me you email add, ill give you the sheet.
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Altough not related directly to the question, why are the result correct ? I'm off by 3, starting on frame 2 ! shouldn't it be 47 ? (27 + 10 + 7 + 3) ?
 

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
All,

Juan yes the score should be 47.

Sixth Sense, your code is correct until the last frame.
I haven't tested very much but:

9 - Spare across the board should be 199 not 190.
Book2
ABCDEFGHIJKLMNOP
112345
2Ian9s199s389s579s769s95
Sheet1
Book2
QRSTUVWXYZAAABACADAEAF
1678910
29s1149s1339s1529s1719s9190
Sheet1


BTW Still very nice work.

Regards,
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,453
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top