Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

VBA help! probably an easy fix

mykasdmykasd Member Posts: 1
I am trying to create a little tool to keep track of my golf scores every round. I am a VBA novice and I am currently stuck as to how to get it to keep track for multiple rounds. I would like it so the Par's/birdies/etc. for a particular hole add up over multiple rounds. Here is the coding I am using:

Sub Update()

'Define variables
Dim Hole() As String, Par() As Integer, Eagle() As Integer, Birdie() As Integer, Bougey() As Integer, _
DoubleB() As Integer, Other() As Integer, NRounds As Integer

'counts how many rounds of golf I have played
Range("D2").Select
Range(Selection, Selection.End(xlToRight)).Select
NRounds = Selection.Columns.Count - 1
Range("A6").Select
ActiveCell.FormulaR1C1 = NRounds & " rounds of golf"


For j = 1 To NRounds

'Finds over/under on par for each hole
'moves latest round to AZ column
Range("E2:E3").Offset(0, NRounds - 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AZ2").Select
ActiveSheet.Paste
'finds difference from par with latest round and puts it in
Range("A22").Select
ActiveCell.FormulaR1C1 = "=R[-20]C[51]-R[-20]C[2]"
Range("A22").Select
Selection.AutoFill Destination:=Range("A22:A39"), Type:=xlFillDefault


'Hole i Stats
For i = 1 To 18

ReDim Preserve Hole(i)
ReDim Preserve Par(i)
ReDim Preserve Birdie(i)
ReDim Preserve Eagle(i)
ReDim Preserve Bougey(i)
ReDim Preserve DoubleB(i)
ReDim Preserve Other(i)

'defines hole(i) as the difference in par for the ith hole
Hole(i) = Range("A22").Offset(i - 1, 0)

'Eagle
If Hole(i) = -2 Then
Eagle(i) = Eagle(i) + 1
ElseIf Hole(i) = -1 Then
Birdie(i) = Birdie(i) + 1
ElseIf Hole(i) = 0 Then
Par(i) = Par(i) + 1
ElseIf Hole(i) = 1 Then
Bougey(i) = Bougey(i) + 1
ElseIf Hole(i) = 2 Then
DoubleB(i) = DoubleB(i) + 1
ElseIf Hole(i) > 2 Then
Other(i) = Other(i) + 1
End If

'displays the results for each hole
With Range("C24").Offset((i - 1) * 7, 0)
.Offset(0, 0) = Eagle(i)
.Offset(1, 0) = Birdie(i)
.Offset(2, 0) = Par(i)
.Offset(3, 0) = Bougey(i)
.Offset(4, 0) = DoubleB(i)
.Offset(5, 0) = Other(i)
End With


Next i

'puts stats into correct columns
Range("C31:C36").Select
Selection.Cut
Range("E24").Select
ActiveSheet.Paste
Range("C38:C43").Select
Selection.Cut
Range("G24").Select
ActiveSheet.Paste
Range("C45:C50").Select
Selection.Cut
Range("I24").Select
ActiveSheet.Paste
Range("C52:C57").Select
Selection.Cut
Range("K24").Select
ActiveSheet.Paste
Range("C59:C64").Select
Selection.Cut
Range("M24").Select
ActiveSheet.Paste

Range("C66:C71").Select
Selection.Cut
Range("C31").Select
ActiveSheet.Paste
Range("C73:C78").Select
Selection.Cut
Range("E31").Select
ActiveSheet.Paste
Range("C80:C85").Select
Selection.Cut
Range("G31").Select
ActiveSheet.Paste
Range("C87:C92").Select
Selection.Cut
Range("I31").Select
ActiveSheet.Paste
Range("C94:C99").Select
Selection.Cut
Range("K31").Select
ActiveSheet.Paste
Range("C101:C106").Select
Selection.Cut
Range("M31").Select
ActiveSheet.Paste

Range("C108:C113").Select
Selection.Cut
Range("C38").Select
ActiveSheet.Paste
Range("C115:C120").Select
Selection.Cut
Range("E38").Select
ActiveSheet.Paste
Range("C122:C127").Select
Selection.Cut
Range("G38").Select
ActiveSheet.Paste
Range("C129:C134").Select
Selection.Cut
Range("I38").Select
ActiveSheet.Paste
Range("C136:C141").Select
Selection.Cut
Range("K38").Select
ActiveSheet.Paste
Range("C143:C148").Select
Selection.Cut
Range("M38").Select
ActiveSheet.Paste

Next j
Sign In or Register to comment.