Sub breakTie(MyAddAry() As String)
Dim myval As Variant
Dim i As Integer, J As Integer, k As Integer, y As Integer, x As Integer, cnt As Integer, lows As Integer
Dim adr As String
Dim jj As Integer, kk As Integer
Dim colgross As Long, rowstart As Long
Dim tmpAdd As String, tmpVal As Integer
Dim ItemElement As Long, tmpindex As Long
Dim lCtr As Long
Dim lTop As Long
Dim lBottom As Long
Dim bckNineTotalLows As Variant
Dim BckLastSix As Integer, BckLastThree As Integer, BckNineTotal As Integer, BckNineLastHole As Integer
Dim BckLastSixArr() As Long, BckLastThreeArr() As Integer, BckNineTotalArr() As Variant, BckNineLastHoleArr() As Integer
Dim BckLastSixAdd() As String, BckLastThreeAdd() As String, BckNineTotalAdd() As Variant, BckNineLastHoleAdd() As String
Dim FrontLastSix As Integer, FrontLastThree As Integer, FrontNineTotal As Integer, FrontNineLastHole As Integer
Dim FrontLastSixArr() As Integer, FrontLastThreeArr() As Integer, FrontNineTotalArr() As Integer, FrontNineLastHoleArr() As Integer
Dim FrontLastSixAdd() As String, FrontLastThreeAdd() As String, FrontNineTotalAdd() As String, FrontNineLastHoleAdd() As String
Dim myArry() As Variant
ReDim BckLastSixArr(LBound(MyAddAry) To UBound(MyAddAry))
ReDim BckLastThreeArr(LBound(MyAddAry) To UBound(MyAddAry))
ReDim BckNineTotalArr(LBound(MyAddAry) To UBound(MyAddAry))
ReDim BckNineLastHoleArr(LBound(MyAddAry) To UBound(MyAddAry))
ReDim BckLastSixAdd(LBound(MyAddAry) To UBound(MyAddAry))
ReDim BckLastThreeAdd(LBound(MyAddAry) To UBound(MyAddAry))
ReDim BckNineTotalAdd(LBound(MyAddAry) To UBound(MyAddAry))
ReDim BckNineLastHoleAdd(LBound(MyAddAry) To UBound(MyAddAry))
ReDim FrontLastSixArr(LBound(MyAddAry) To UBound(MyAddAry))
ReDim FrontLastThreeArr(LBound(MyAddAry) To UBound(MyAddAry))
ReDim FrontNineTotalArr(LBound(MyAddAry) To UBound(MyAddAry))
ReDim FrontNineLastHoleArr(LBound(MyAddAry) To UBound(MyAddAry))
ReDim FrontLastSixAdd(LBound(MyAddAry) To UBound(MyAddAry))
ReDim FrontLastThreeAdd(LBound(MyAddAry) To UBound(MyAddAry))
ReDim FrontNineTotalAdd(LBound(MyAddAry) To UBound(MyAddAry))
ReDim FrontNineLastHoleAdd(LBound(MyAddAry) To UBound(MyAddAry))
ReDim myArry(LBound(MyAddAry) To UBound(MyAddAry), 1 To 8)
'################### Collect all values to break ties, last nine, sum of last 6, and sum of last 3 etc.
For i = LBound(MyAddAry) To UBound(MyAddAry)
BckNineTotal = 0
BckLastSix = 0
BckLastThree = 0
BckNineLastHole = 0
FrontLastSix = 0
FrontLastThree = 0
FrontNineTotal = 0
FrontNineLastHole = 0
adr = MyAddAry(i)
If adr = "" Then ' just in case an array element does not have a cell address
Else
Range(adr).Select 'just a check for me not required
'Get back nine values for evaluation
BckNineTotal = Range(adr).Offset(0, -1) ' get backnine total
' BckNineTotalAdd(i) = MyAddAry(i)
BckNineLastHole = Range(adr).Offset(0, -3) 'get last hole value
' BckNineLastHoleAdd(i) = MyAddAry(i)
For k = 3 To 8 ' Get total scores of the last six holes
BckLastSix = BckLastSix + Range(adr).Offset(0, -k)
Next k
' BckLastSixAdd(i) = MyAddAry(i)
k = 0
For k = 3 To 5 'Get total of the last 3 holes
BckLastThree = BckLastThree + Range(adr).Offset(0, -k)
Next k
' BckLastThreeAdd(i) = MyAddAry(i)
k = 0
'Get front nine values for evaluation
FrontNineTotal = Range(adr).Offset(0, -2) 'get front nine total
' FrontNineTotalAdd(i) = MyAddAry(i)
FrontNineLastHole = Range(adr).Offset(0, -12) ' get last hole value on front nine
' FrontNineLastHoleAdd(i) = MyAddAry(i)
For k = 12 To 17 ' Get total scores of the last six holes on the front nine
FrontLastSix = FrontLastSix + Range(adr).Offset(0, -k)
Next
' FrontLastSixAdd(i) = MyAddAry(i)
k = 0
For k = 12 To 14 'Get total of the last 3 holes on the front 9
FrontLastThree = FrontLastThree + Range(adr).Offset(0, -k)
Next
BckLastSixArr(i) = BckLastSix
BckLastThreeArr(i) = BckLastThree
BckNineTotalArr(i) = BckNineTotal
BckNineLastHoleArr(i) = BckNineLastHole
FrontLastSixArr(i) = FrontLastSix
FrontLastThreeArr(i) = FrontLastThree
FrontNineTotalArr(i) = FrontNineTotal
FrontNineLastHoleArr(i) = FrontNineLastHole
End If
myArry(i, 1) = adr
myArry(i, 2) = BckNineTotal
myArry(i, 3) = BckLastSix
myArry(i, 4) = BckLastThree
myArry(i, 5) = BckNineLastHole
myArry(i, 6) = FrontLastSix
myArry(i, 7) = FrontLastThree
myArry(i, 8) = FrontNineLastHole
Next
' Check back nine total for low value
J = 0 '########################################################################
lows = WorksheetFunction.Min(BckNineTotalArr)
tmpVal = UBound(MyAddAry) 'count of how many addresses were passed
For x = LBound(MyAddAry) To UBound(MyAddAry)
With Application.WorksheetFunction
bckNineTotalLows = .Min(myArry(x, 2))
End With
For k = 1 To UBound(MyAddAry)
If myArry(k, 2) = lows Then
xx = xx + 1
End If
Next
If bckNineTotalLows = lows Then
J = J + 1
adr = myArry(x, x)
Else
y = y + 1 'need to do check on remaining values
End If
If J = 1 Then
Range(adr).Select
Range(adr).Interior.ColorIndex = 3
Range(adr).Offset(0, 1).Interior.ColorIndex = 4
Range(adr).Offset(0, 2).Value = 1
Range(adr).Offset(0, 3).Value = 0
ElseIf J > 1 Then 'if more then 1 low value
lows = WorksheetFunction.Min(BckLastSixArr)
With Application.WorksheetFunction
bckSixTotalLows = .Min(myArry(x, 3))
End With
If bckNineTotalLows = lows Then
J = J + 1
adr = myArry(x, x)
Else
y = y + 1 'need to do check on remaining values
End If
End If
Next
Application.Run "Sheet2.CheckforDups"
End Sub
. I'm taking some courses online, but I'm definitely not up to understanding what you've done here. BUT, learning is good, so can you provide some explanation with your provided code.
