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....
thanks in advance
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