Option Explicit
Dim arrTeam() As Variant
Dim arrRate() As Variant
Dim arrRslt() As Variant
Sub GameResults()
arrTeam = ActiveSheet.UsedRange.Columns(4).Resize(, 2).Value
arrRate = ActiveSheet.UsedRange.Columns(7).Resize(, 6).Value
arrRslt = Range("N2").CurrentRegion.Value
CountGames
Range("N2").CurrentRegion.Value = arrRslt
End Sub
Private Sub CountGames()
Dim x As Long, y As Long, z As Long
For z = 2 To UBound(arrRslt, 1)
For y = 2 To UBound(arrRslt, 2)
arrRslt(z, y) = 0
Next y
For x = 3 To UBound(arrTeam, 1)
If arrTeam(x, 1) = arrRslt(z, 1) Or arrTeam(x, 2) = arrRslt(z, 1) Then
If Val(arrRate(x, 5)) + Val(arrRate(x, 6)) > 0 Then
arrRslt(z, 2) = arrRslt(z, 2) + 1
If arrTeam(x, 1) = arrRslt(z, 1) Then
Select Case arrRate(x, 5)
Case 3
arrRslt(z, 3) = arrRslt(z, 3) + arrRslt(z, 3) + 1
Case 0
arrRslt(z, 4) = arrRslt(z, 4) + arrRslt(z, 4) + 1
Case 1
arrRslt(z, 5) = arrRslt(z, 5) + arrRslt(z, 5) + 1
End Select
arrRslt(z, 6) = arrRslt(z, 6) + arrRate(x, 1)
arrRslt(z, 7) = arrRslt(z, 7) + arrRate(x, 2)
arrRslt(z, 8) = arrRslt(z, 8) + arrRate(x, 3)
arrRslt(z, 9) = arrRslt(z, 9) + arrRate(x, 4)
arrRslt(z, 10) = arrRslt(z, 10) + arrRate(x, 5)
Else
Select Case arrRate(x, 6)
Case 3
arrRslt(z, 3) = arrRslt(z, 3) + arrRslt(z, 3) + 1
Case 0
arrRslt(z, 4) = arrRslt(z, 4) + arrRslt(z, 4) + 1
Case 1
arrRslt(z, 5) = arrRslt(z, 5) + arrRslt(z, 5) + 1
End Select
arrRslt(z, 6) = arrRslt(z, 6) + arrRate(x, 2)
arrRslt(z, 7) = arrRslt(z, 7) + arrRate(x, 1)
arrRslt(z, 8) = arrRslt(z, 8) + arrRate(x, 4)
arrRslt(z, 9) = arrRslt(z, 9) + arrRate(x, 3)
arrRslt(z, 10) = arrRslt(z, 10) + arrRate(x, 6)
End If
End If
End If
Next x
Next z
End Sub