bowling score help

golf401 · Feb 21, 2004

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

SIXTH SENSE · Feb 23, 2004

Ian Mac said:
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
A B C D E F G H I J K L M N O P
1 1 2 3 4 5
2 Ian 9 s 19 9 s 38 9 s 57 9 s 76 9 s 95
Sheet1
Book2
Q R S T U V W X Y Z AA AB AC AD AE AF
1 6 7 8 9 10
2 9 s 114 9 s 133 9 s 152 9 s 171 9 s 9 190
Sheet1

BTW Still very nice work.

Regards,

Mac,
Check the checker given by golf401. its says 190!

Ian Mac · Feb 23, 2004

Was just going to post and apologies for my error, and say that Sixth Sense's answer was actually correct and very good

All that, and BTW Sensey! it's Ian or Ian Mac

SIXTH SENSE · Feb 23, 2004

Ian Mac said:
Was just going to post and apologies for my error, and say that Sixth Sense's answer was actually correct and very good

All that, and BTW Sensey! it's Ian or Ian Mac

Sorry for that Ian.
My setmate here in office is MAC. I suppose you got
the same nicknames!

Jay Petrulis · Feb 23, 2004

Hi,

Not code, but here is a workbook for bowling.

ftp://members.aol.com/hrlngrv/bowling.xls

The author has an AOL username, hrlngrv, and is my favorite Excel guru. If you figure out who he is from his moniker, you will know who I mean and why I say he's my favorite.

NateO · Feb 23, 2004

Jay Petrulis said:
The author has an AOL username, hrlngrv, and is my favorite Excel guru. If you figure out who he is from his moniker, you will know who I mean and why I say he's my favorite.

Hmmm, who could we be talking about here?

Sorry, off-topic somewhat, but had to guess.

SIXTH SENSE · Feb 23, 2004

Hi!
golf!
Here i am again!
if you insist this set up, then try again!

Bowling.xls

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

Players

Frame

2

1

2

3

4

5

6

7

8

9

10

3

1

X

x

0

x

X

4

30

60

90

120

150

180

210

240

270

300

5

6

2

9

S

9

S

9

S

9

S

9

S

9

S

9

S

9

S

9

S

9

S

9

7

19

38

57

76

95

114

133

152

171

190

8

Bowling2

The code!

This goes to the standard module!!!Type BallStatus1
 StrikeAsBoolean
 SpareAsBoolean
 ScoreAsIntegerEndTypeType Oneframe1
 Thisround(0 To 1)As BallStatus1EndTypeSub Scoring2(ByVal PlayerNumberAsInteger)Dim MyScore(9)As Oneframe1Dim MyScorePerFrame(9)AsIntegerDim colxAsIntegerDim BunosAsInteger
colx = 2
Rowx = 3 * PlayerNumberFor I =LBound(MyScore)ToUBound(MyScore)SelectCase UCase(Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value)
 Case "X"
 MyScore(I).Thisround(0).Strike =True
 MyScore(I).Thisround(0).Spare =False
 MyScore(I).Thisround(0).Score = 10
 MyScore(I).Thisround(1).Strike =False
 MyScore(I).Thisround(1).Spare =False
 MyScore(I).Thisround(1).Score = 0
 CaseElse
 MyScore(I).Thisround(0).Score = Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value
 EndSelectIf MyScore(I).Thisround(0).Strike =FalseThen
 TMP = UCase(Range(Cells(Rowx, colx + 1), Cells(Rowx, colx + 1)).Value)
 SelectCase TMP
 Case "S"
 MyScore(I).Thisround(1).Strike =False
 MyScore(I).Thisround(1).Spare =True
 MyScore(I).Thisround(1).Score = 10 - MyScore(I).Thisround(0).Score
 CaseElse
 MyScore(I).Thisround(1).Strike =False
 MyScore(I).Thisround(1).Spare =False
 MyScore(I).Thisround(1).Score = Range(Cells(Rowx, colx + 1), Cells(Rowx, colx + 1)).Value
 EndSelectEndIf
colx = colx + 2Next I
TMP = Range(Cells(Rowx, colx - 1), Cells(Rowx, colx - 1)).ValueSelectCase UCase(TMP)
Case "X"
 MyScore(UBound(MyScore)).Thisround(1).Score = 10
 MyScore(UBound(MyScore)).Thisround(1).Strike =True
 MyScore(UBound(MyScore)).Thisround(1).Spare =False
 If UCase(Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value) = "X"Then
 Bunos = 10
 Else
 Bunos = Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value
 EndIf
Case "S"
 MyScore(UBound(MyScore)).Thisround(1).Score = 10 - MyScore(UBound(MyScore)).Thisround(0).Score
 MyScore(UBound(MyScore)).Thisround(1).Strike =False
 MyScore(UBound(MyScore)).Thisround(1).Spare =True
 If UCase(Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value) = "X"Then
 Bunos = 10
 Else
 Bunos = Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value
 EndIf
CaseElse
 MyScore(UBound(MyScore)).Thisround(1).Score = Range(Cells(Rowx, colx - 1), Cells(Rowx, colx - 1)).Value
 MyScore(UBound(MyScore)).Thisround(1).Strike =False
 MyScore(UBound(MyScore)).Thisround(1).Spare =False
 Bunos = 0EndSelectFor I =LBound(MyScore)ToUBound(MyScore) - 1
 If MyScore(I).Thisround(0).Strike =TrueThen
 If MyScore(I + 1).Thisround(0).StrikeThen
 If I =UBound(MyScore) - 1Then
 MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I + 1).Thisround(0).Score + MyScore(I + 1).Thisround(1).Score
 Else
 MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I + 1).Thisround(0).Score + MyScore(I + 2).Thisround(0).Score
 EndIf
 Else
 MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I + 1).Thisround(0).Score + MyScore(I + 1).Thisround(1).Score
 EndIf

 ElseIf MyScore(I).Thisround(1).Spare =TrueThen
 MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I).Thisround(1).Score + MyScore(I + 1).Thisround(0).Score
 ElseIf (MyScore(I).Thisround(0).Strike =False) And (MyScore(I).Thisround(1).Spare =False)Then
 MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I).Thisround(1).Score
 EndIfNext I
MyScorePerFrame(UBound(MyScore)) = MyScore(UBound(MyScore)).Thisround(0).Score + MyScore(UBound(MyScore)).Thisround(1).Score + Bunos
TMP = 0'output score
colx = 2
Rowx = PlayerNumber * 3 + 1For I =LBound(MyScore)To UBound(MyScore) - 1
 TMP = MyScorePerFrame(I) + TMP
 Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value = TMP
 colx = colx + 2Next I
 Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value = TMP + MyScorePerFrame(UBound(MyScore))EndSub

And this goes the sheet module!!!PrivateSub Worksheet_Change(ByVal TargetAs Range)OnErrorGoTo en
Application.EnableEvents =False
Application.ScreenUpdating =FalseSet isect = Application.Intersect(Target, Range("b2:V11"))
 IfNot isectIsNothingThen
 PlayerNumber = ActiveCell.Row / 3
 Call Scoring2(PlayerNumber)
 EndIf
en:If Err.Number<> 0Then
 MsgBox "Error occurs" & Chr(13) & Err.NumberEndIf
Application.ScreenUpdating =True
Application.EnableEvents =TrueEndSub

Note:
Just tested very quickly. but i think it looks fine!

Jay Petrulis · Feb 23, 2004

NateO said:
Jay Petrulis said:

The author has an AOL username, hrlngrv, and is my favorite Excel guru. If you figure out who he is from his moniker, you will know who I mean and why I say he's my favorite.

Click to expand...

Hmmm, who could we be talking about here?

Sorry, off-topic somewhat, but had to guess.

Was it that easy? :wink: A perfect reference to the legend that is Harlan Grove.

Harlan is as good as Aladin (possibly better) at native excel formulas -- that *is* saying a helluva lot. He is a supreme coder in VBA -- easily on par with the best of them. He likely knows mathematics/statistics/probability at the level of the best of the Excel math whizzes (Dave Braden, Jerrry L. Lewis, et al). He knows all the spreadsheet options (1-2-3, quattro pro, Gnumeric, Star/OpenOffice, etc.). He knows other languages (and participates in the awk groups just as he does in the Excel ones).

But the best of all is his personality. If you read the Harlan Chronicles, you get free entertainment. Unless, of course, you are on the receiving end of his wrath. He is called the newsgroup curmudgeon for a really good reason. Ponsy Nob. is good, too, but I'll take the Harlan barbs at this stage of their careers.

The only technical weakness is that he may not be up to the level of a Jon Peltier, Tushar Mehta, or an Andy Pope at graphing. He may be, but he hasn't jumped in any charting discussions that I have seen.

My first trip every day to the world of Excel is to search google for Harlan Grove as the key terms. Only then do I enter MrExcel.

golf401 · Feb 29, 2004

Not to be rude........... but what part of this topic have anything to do with bragging about someone? I thought this topic was about "bowling", anyways, the file did work well thanks.

golf401 · Feb 29, 2004

Hey Sixth Sense, thank you for taking so much time on this. I tried running this program and i keep getting an error about the type?
"Compile error:
Cannot define a Public user-defined type within an object module"

The scores arent coming up, for some reason I cant figure it out.
thanks in advance!

NateO · Mar 1, 2004

golf401 said:
Not to be rude........... but what part of this topic have anything to do with bragging about someone? I thought this topic was about "bowling", anyways, the file did work well thanks.

Not at all. The brief dialogue was assigning credit, and then some, to the relevant file that you reference as working well.

bowling score help

golf401

New Member

SIXTH SENSE

Well-known Member

Excel Facts

Ian Mac

MrExcel MVP

SIXTH SENSE

Well-known Member

Jay Petrulis

MrExcel MVP

NateO

Legend

SIXTH SENSE

Well-known Member

Jay Petrulis

MrExcel MVP

golf401

New Member

golf401

New Member

NateO

Legend

Similar threads

Forum statistics

Share this page

bowling score help

New Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Legend

Well-known Member

MrExcel MVP

New Member

New Member

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock