CODEWITHSUNDEEP
vbaexcelexcel-2007excel-2003
user3335033
user3335033

Reputation: 11

Excel 2007 to Excel 2003 VBA "Sort" issue

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!)

Upvotes: 1

Views: 795

Answers (3)

Bethany Maloney
Bethany Maloney

Reputation: 41

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

Upvotes: 0

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

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

Upvotes: 0

Jacob Lambert
Jacob Lambert

Reputation: 7679

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.

Upvotes: 1

Related Questions