Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sortTable works in 2003 but not in 2007
Hi
I am a newbie and received a file use VB to create a league table. The results are entered on a separate sheet and the league table updates automatically when the league table worksheet is selected. Great in 2003 but when i open the file in 2007, i get a "Compile error, invalid use of property". Basically the code below seems to be the problem, but i have no idea how to correct it. Private Sub sortTable() sort sortRange:=Table.Range("leagueTable"), _ sortKey1:=Range("A4"), _ sortKey2:=Range("Y4"), _ sortOrder2:=xlDescending, _ sortKey3:=Range("W4") ' Range("B3").Select End Sub Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sortTable works in 2003 but not in 2007
Excel 2007 had a different model for doing sorts. It allowed more than 3
keys, so has collection of sort keys. Turn on the macro recorder and set up a multikey sort manually. Then turn of the macro recorder and see what the code looks like. I don't have xl2007 installed, so I can't give you specifics. -- Regards, Tom Ogilvy "Marcello" wrote: Hi I am a newbie and received a file use VB to create a league table. The results are entered on a separate sheet and the league table updates automatically when the league table worksheet is selected. Great in 2003 but when i open the file in 2007, i get a "Compile error, invalid use of property". Basically the code below seems to be the problem, but i have no idea how to correct it. Private Sub sortTable() sort sortRange:=Table.Range("leagueTable"), _ sortKey1:=Range("A4"), _ sortKey2:=Range("Y4"), _ sortOrder2:=xlDescending, _ sortKey3:=Range("W4") ' Range("B3").Select End Sub Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sortTable works in 2003 but not in 2007
Created a simple macro as suggested. This is a copy of the code that was
produced. Sub Macro3() ' ' Macro3 Macro ' ' Application.CutCopyMode = False ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Clear ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add Key:=Range("D10:D31") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add Key:=Range("E10:E31") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add Key:=Range("F10:F31") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet3").sort .SetRange Range("D10:L31") .Header = xlGuess .MatchCase = False .orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Had a look through the code and it is not apparent what part of it i can use? Thanks in advance for your help. "Tom Ogilvy" wrote: Excel 2007 had a different model for doing sorts. It allowed more than 3 keys, so has collection of sort keys. Turn on the macro recorder and set up a multikey sort manually. Then turn of the macro recorder and see what the code looks like. I don't have xl2007 installed, so I can't give you specifics. -- Regards, Tom Ogilvy "Marcello" wrote: Hi I am a newbie and received a file use VB to create a league table. The results are entered on a separate sheet and the league table updates automatically when the league table worksheet is selected. Great in 2003 but when i open the file in 2007, i get a "Compile error, invalid use of property". Basically the code below seems to be the problem, but i have no idea how to correct it. Private Sub sortTable() sort sortRange:=Table.Range("leagueTable"), _ sortKey1:=Range("A4"), _ sortKey2:=Range("Y4"), _ sortOrder2:=xlDescending, _ sortKey3:=Range("W4") ' Range("B3").Select End Sub Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sortTable works in 2003 but not in 2007
My guess would be:
Private Sub sortTable Activesheet.sort.SortFields.Clear Activesheet.sort.SortFields.Add Key:=Range("A4"), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal Activesheet.sort.SortFields.Add Key:=Range("Y4"), _ SortOn:=xlSortOnValues, _ Order:=xlDescending, _ DataOption:=xlSortNormal Activesheet.sort.SortFields.Add Key:=Range("W4"), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal With ActiveSheet.sort .SetRange Table.Range("LeagueTable") .Header = xlGuess .MatchCase = False .orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards, Tom Ogilvy "Marcello" wrote: Created a simple macro as suggested. This is a copy of the code that was produced. Sub Macro3() ' ' Macro3 Macro ' ' Application.CutCopyMode = False ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Clear ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add Key:=Range("D10:D31") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add Key:=Range("E10:E31") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add Key:=Range("F10:F31") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet3").sort .SetRange Range("D10:L31") .Header = xlGuess .MatchCase = False .orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Had a look through the code and it is not apparent what part of it i can use? Thanks in advance for your help. "Tom Ogilvy" wrote: Excel 2007 had a different model for doing sorts. It allowed more than 3 keys, so has collection of sort keys. Turn on the macro recorder and set up a multikey sort manually. Then turn of the macro recorder and see what the code looks like. I don't have xl2007 installed, so I can't give you specifics. -- Regards, Tom Ogilvy "Marcello" wrote: Hi I am a newbie and received a file use VB to create a league table. The results are entered on a separate sheet and the league table updates automatically when the league table worksheet is selected. Great in 2003 but when i open the file in 2007, i get a "Compile error, invalid use of property". Basically the code below seems to be the problem, but i have no idea how to correct it. Private Sub sortTable() sort sortRange:=Table.Range("leagueTable"), _ sortKey1:=Range("A4"), _ sortKey2:=Range("Y4"), _ sortOrder2:=xlDescending, _ sortKey3:=Range("W4") ' Range("B3").Select End Sub Any help would be greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sortTable works in 2003 but not in 2007
Hi Tom
Had a go using the code you suggested, but it still didn't work. Have played around with it for some time now and finally it works, a copy of the code is below: Private Sub sortTable() ActiveWorkbook.Worksheets("League Table").sort.SortFields.Clear ActiveWorkbook.Worksheets("League Table").sort.SortFields.Add Key:=Range( _ "A3:A24"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("League Table").sort.SortFields.Add Key:=Range( _ "Y3:Y24"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("League Table").sort.SortFields.Add Key:=Range( _ "W3:W24"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("League Table").sort .SetRange Range("A2:Z24") .Header = xlYes .MatchCase = False .orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Thanks for you help, without it I would never have been on the right path. Cheers "Tom Ogilvy" wrote: My guess would be: Private Sub sortTable Activesheet.sort.SortFields.Clear Activesheet.sort.SortFields.Add Key:=Range("A4"), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal Activesheet.sort.SortFields.Add Key:=Range("Y4"), _ SortOn:=xlSortOnValues, _ Order:=xlDescending, _ DataOption:=xlSortNormal Activesheet.sort.SortFields.Add Key:=Range("W4"), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal With ActiveSheet.sort .SetRange Table.Range("LeagueTable") .Header = xlGuess .MatchCase = False .orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards, Tom Ogilvy "Marcello" wrote: Created a simple macro as suggested. This is a copy of the code that was produced. Sub Macro3() ' ' Macro3 Macro ' ' Application.CutCopyMode = False ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Clear ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add Key:=Range("D10:D31") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add Key:=Range("E10:E31") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add Key:=Range("F10:F31") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet3").sort .SetRange Range("D10:L31") .Header = xlGuess .MatchCase = False .orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Had a look through the code and it is not apparent what part of it i can use? Thanks in advance for your help. "Tom Ogilvy" wrote: Excel 2007 had a different model for doing sorts. It allowed more than 3 keys, so has collection of sort keys. Turn on the macro recorder and set up a multikey sort manually. Then turn of the macro recorder and see what the code looks like. I don't have xl2007 installed, so I can't give you specifics. -- Regards, Tom Ogilvy "Marcello" wrote: Hi I am a newbie and received a file use VB to create a league table. The results are entered on a separate sheet and the league table updates automatically when the league table worksheet is selected. Great in 2003 but when i open the file in 2007, i get a "Compile error, invalid use of property". Basically the code below seems to be the problem, but i have no idea how to correct it. Private Sub sortTable() sort sortRange:=Table.Range("leagueTable"), _ sortKey1:=Range("A4"), _ sortKey2:=Range("Y4"), _ sortOrder2:=xlDescending, _ sortKey3:=Range("W4") ' Range("B3").Select End Sub Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA errors in 2007 (works fine in 2003) | Excel Discussion (Misc queries) | |||
SUMIF formula works in Excel 2003, does not work in Excel 2007 | Excel Discussion (Misc queries) | |||
function =IFERROR LOOKUP works in excel 2007 not in excel 2003 | Excel Worksheet Functions | |||
Works in 2007 but not 2003 | Excel Discussion (Misc queries) | |||
xls file works fine in Excel 2000 and 2007 but crashes on opening in 2003 | Excel Discussion (Misc queries) |