Excel 2007 to Excel 2003 VBA "Sort" issue

1
votes

I've inherited an excel spreadsheet which when run on excel 2007 works fine. the problem is the computer it was on died and the new machine only has excel 2003 - the spreadsheet no longer works correctly. the issue is the "sort" function in a bit of vba code.

I wondered if anyoe knew if I can change it to work in excel 2003? I;ve seen similar posts but I'm a total novice when it comes to vba - I'm just looking for a simple "copy and paste" solution - replacing the 2007 bit with the 2003 bit. Everythig else works.

Heres the code - 

'Sort by Class then by Entry No
ActiveWorkbook.Worksheets("Competitor & Class Entry").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Competitor & Class Entry").Sort.SortFields.Add Key _
    :=Range(ClassRange1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
ActiveWorkbook.Worksheets("Competitor & Class Entry").Sort.SortFields.Add Key _
    :=Range("A9:A308"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With ActiveWorkbook.Worksheets("Competitor & Class Entry").Sort
    .SetRange Range("A9:AE308")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Any help greatly appreciated! (along with how to insert it!)

3
vbaexcelexcel-2007excel-2003
I know its the "Sorton" command thats not supported in 2003. The code basically sorts a list of competitors in to their respective classes and then in to their respective entry numbers so I can print of a start list for each class. – user3335033

3 Answers

1
votes

My suggestion would be to use the macro recorder once on the data you want to sort and modify the saved code to suit your needs.

0
votes

not sure if any need, but 3 times in the code , you use a range without specifying the sheet:

ActiveWorkbook.Worksheets("Competitor & Class Entry").

before

:=Range("A9:A308"), SortOn:=

and

.SetRange Range("A9:AE308")
.Add Key :=Range(ClassRange1)

Anyway I'd rewrite the code with :

Dim Sh as worksheet
set sh=ActiveWorkbook.Worksheets("Competitor & Class Entry")

with Sh.Sort
    with .SortFields
        .Clear
        .Add Key :=SH.Range(ClassRange1), SortOn:=xlSortOnValues, Order:=xlAscending, _
                    DataOption :=xlSortNormal
        .Add Key :=sh.Range("A9:A308"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                    DataOption :=xlSortNormal
    end with
    .SetRange sh.Range("A9:AE308")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

set Sh=nothing
0
votes

The following should work. I guess they added a new sort function in Excel 2007.

With ActiveWorkbook.Worksheets("Competitor & Class Entry")
    .Range("A9:AE308").Sort Key1:=.Range(ClassRange1), Order1:=xlAscending, Key2:=.Range( _
        "A9:A308"), Order2:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
End With