Auto sort

[MAX]

I created a table with a range of "A1:I6".

In row 1 starting from A1 I have these headings:
Team Play Win Draw Lose GF GA GD Points

In column A starting from A2 I have these names:
Roma
Milan
Lazio
Bari
Siena

The table is updated from such fixtures in the same sheet and all I want is
that when I post the results in these fixtures, the table sort automatically
like this.

Column: Sort On: Order:
Sort by: Points Values Z to A
Then by: GD Values Largest to Smallest
Then by: GF Values Largest to Smallest

( Range to sort is A2:I6).

I attached the code (below) that was given to me by MASTER Shane, but no
positive results. I know that he is right but I thing that I am doing
something wrong. This is what I am doing:
With the fixtures and table already on the sheet (Excel 2007),
Click on Developer > Visual Basic and then copy the code in the space
provided.
Then go back to the sheet to post some results in the fixtures so that for
example the points will change, but as I give the first result, the Visual
Basic appears with a window saying Compile error and Syntax error. Then when
I close this error window, I keep posting the results. There the table
becomes updated but not sorted.

CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("F2:I6"))
If Not isect Is Nothing Then
With ActiveSheet
With .Sort.SortFields
.Clear
.Add Key:=Range("I2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
.Add Key:=Range("H2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
.Add Key:=Range("F2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
End With
With .Sort
.SetRange Range("A1:I6")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End If
End Sub

 

[Shane Devenshire]

H5,

I have tested this code on a second machine and it works fine. The code
belongs in the Sheet object for the sheet inwhich to data is being entered.
In the VBA (Alt+F11) select your file from the Project window, double-click
the appropriate sheet and paste in the code. I notice that you have added a
Enter after each of the xlDescending, lines to push the code to the next
line, you can't do that. If you want to continue a line of code you on a
different line you must use the line continuation character "space
underscore". The original code did not need that because it was part of the
same line.

In otherwords, if you keep the line the way you show below you must modify
it from

SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal

to

SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal

Cheers,
Shane

 

[MAX]

Thanks for your very great help MASTER.

H5,

I have tested this code on a second machine and it works fine. The code
belongs in the Sheet object for the sheet inwhich to data is being entered.
In the VBA (Alt+F11) select your file from the Project window, double-click
the appropriate sheet and paste in the code. I notice that you have added a
Enter after each of the xlDescending, lines to push the code to the next
line, you can't do that. If you want to continue a line of code you on a
different line you must use the line continuation character "space
underscore". The original code did not need that because it was part of the
same line.

In otherwords, if you keep the line the way you show below you must modify
it from

SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal

to

SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal

Cheers,
Shane

 

[MAX]

Sorry Shane, it is doing the same. After I opened the VBA I double - click on
sheet 1 because the table and fixtures are both in sheet 1 and then I copy
and paste the code from your last post or I don't know how to do it.
Sorry again Shane.
Thanks.