Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic sort macro
Hi all
E3:F8 represent the results of soccer matches. G3:O6 are the resulting league positions of the 4 teams in the league. I want the league positions to be updated automatically as the results are entered. Therefore the sort should be on column N firstly (points scored) then column O which is the goal difference then on column L which is goals scored. I recorded a macro which did this and then copied & pasted the code into a Calculate event macro. The recorded macro version works fine but the Calculate event produces a runtime error and reports €œMethod €˜Apply of object €˜Sort failed. Can you tell me what I need to amend in order to get this to work correctly. My code is as below and I would like to thank you for any help you can give. Private Sub Worksheet_Calculate() Range("G3:O6").Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("N3:N6"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("O3:O6"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("L3:L6"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("G3:O6") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Al |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic sort macro
Sorry for delay replying. you puy me on the right lines and now up & running.
Thanks for your help Al "Don Guillett" wrote: I'm not terribly familiar with 2007 sorting macros but Try in a worksheet_change event that restricts to the last column where you enter the data before the sort. -- Don Guillett Microsoft MVP Excel SalesAid Software "gramps" wrote in message ... Hi all E3:F8 represent the results of soccer matches. G3:O6 are the resulting league positions of the 4 teams in the league. I want the league positions to be updated automatically as the results are entered. Therefore the sort should be on column N firstly (points scored) then column O which is the goal difference then on column L which is goals scored. I recorded a macro which did this and then copied & pasted the code into a Calculate event macro. The recorded macro version works fine but the Calculate event produces a runtime error and reports €œMethod €˜Apply of object €˜Sort failed. Can you tell me what I need to amend in order to get this to work correctly. My code is as below and I would like to thank you for any help you can give. Private Sub Worksheet_Calculate() Range("G3:O6").Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("N3:N6"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("O3:O6"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("L3:L6"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("G3:O6") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Al . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic sort | Excel Discussion (Misc queries) | |||
Automatic Sort | Excel Discussion (Misc queries) | |||
Automatic League Sort | Excel Worksheet Functions | |||
Automatic Sort | Excel Discussion (Misc queries) | |||
automatic sort | Excel Worksheet Functions |