# bowling score help

#### golf401

##### New Member
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

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

##### MrExcel MVP
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
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
Continuation of the above sheet!
Bowling.xls
16thFramescore7thFramescore8thFramescore9thFramescore10thFrameScore
2x1152s134901437s163xxx193
3x180x210x240x270xxx300
453502557806571738182
55s690s8780957s1138s8131
633908s110x140x170xxx200
7x180x210x240x270xxx300
80000000000000000
900000
1000000
1100000
12
13
14
Sheet2

#### SIXTH SENSE

##### Well-known Member
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
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.
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
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
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
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
1678910
29s1149s1339s1529s1719s9190
Sheet1

BTW Still very nice work.

Regards,

Replies
1
Views
98
Replies
8
Views
173
Replies
3
Views
152
Replies
6
Views
502
Replies
0
Views
1K

1,187,188
Messages
5,962,108
Members
438,585
Latest member
peterLEE0223

### 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.

### Which adblocker are you using?

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

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