Hi
I have created a macro to populate the Premiership table with a variety of statistics. Part of the code is supposed to sort the teams based on points, goal difference if points are equal, and goals for if points and goal difference are equal.
However, it won't seem to sort based on goal difference unless I remove the part of the code that sorts based on goals for. The sort code is as follows:
For Each cell In team
cell.Activate
'Sort by points
If ActiveCell.Offset(0, 8) <> ActiveCell.Offset(1, 8) Then
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("J1"), Order:=xlDescending
.SetRange Range("B1:CA21")
.Header = xlYes
.Apply
End With
'Sort by goal difference if equal on points
ElseIf (ActiveCell.Offset(0, 8) = ActiveCell.Offset(1, 8)) And (ActiveCell.Offset(0, 7) <> ActiveCell.Offset(1, 7)) Then
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("I1"), Order:=xlDescending
.SetRange Range("B1:CA21")
.Header = xlYes
.Apply
End With
Range("B2").Activate
'Sort by goals for if equal on points and goal difference
ElseIf (ActiveCell.Offset(0, 8) = ActiveCell.Offset(1, 8)) And (ActiveCell.Offset(0, 7) = ActiveCell.Offset(1, 7)) Then
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("G1"), Order:=xlDescending
.SetRange Range("B1:CA21")
.Header = xlYes
.Apply
End With
Range("B2").Activate
End If
Next cell
The problem is shown in this picture:
Error1.PNG
As you can see, the order should be Sheffield United, Burnley and then Arsenal because that is the descending order of goal difference. Instead it is sorted in descending order of goals for.
Note also that Tottenham and Man United should also be swapped.
How can I fix this?
Thanks
Bookmarks