bowling score help

[golf401]

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

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]

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!

hi golf!
you got to paste this in a standard module.

Type BallStatus1
    Strike As Boolean
    Spare As Boolean
    Score As Integer
End Type

I know that this is on the top of your code. but you pasted it in the sheet module. you may transfer this to the standard module and everything will go fine!

 

[golf401]

Got it. Thanks Sixth Sense and everyone else who offered their wisdom.

 

[silaz777]

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.

I have a problem with the last frame... doesnt seems to work if i do not put in X for the 1st box, or / for the 2nd... pls help...

 

[silaz777]

i realised that the last part of the formula is wrong... should be working correctly after i rectified the mistake... thanx

 

[choker]

Hi

I'm stumbled upon your code as a result of a search as i'm trying to build a bowling analysis spreadsheet. I've had a look at the code and included it in a spreadsheet but am getting an incorrect score in the 10th frame. This only occurs if the 10th is a 'X' 'count'(where counts is between 0 - 9) 's'. I'm finding it doesn't include the s and hence, returns an incorrect score. can you help me please on this?