Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto sort
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto sort
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto sort
Thanks for your very great help MASTER.
"Shane Devenshire" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto sort
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. "MAX" wrote: Thanks for your very great help MASTER. "Shane Devenshire" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to Auto sort or save a sort? | Excel Discussion (Misc queries) | |||
Auto-Sort Won't Sort All Column Cells | Excel Discussion (Misc queries) | |||
Auto sort | Excel Worksheet Functions | |||
auto sort? | Excel Discussion (Misc queries) | |||
Auto Sort | Excel Worksheet Functions |