Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    I have a macro that was written on a computer with excel 2007 but was saved as an .xls file (2003). When I run the macro on my computer it works fine but if someone that has 2003 on their laptop opens it and attempts to run the macro the receive an error. When they hit debug it appears to be the " ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear" that is causing the issue for them. Basically, I want to take a few columns and sort them by a specific column. Could you help me write the code so that it won't bomb out on a user with excel 2003? The full code for the selection and sort is below. Thanks.


    Columns("A:F").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range( _
    "E2:E65536"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Columns("A:F")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Roughly:
    Code:
        Range("A:F").Sort Key1:=Range("E2"), Order1:=xlDescending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    MS changed the syntax for sorting in xl2007.
    It added no real value. They have no shame.
    The following should work in all XL versions from xl97 thru xl2007.
    There may be other "improvements" in xl2010 ?
    '--
    Sub UniversalSort()
    With ActiveSheet
    .Columns("A:F").Sort key1:=.Range("E2"), _
    order1:=xlDescending, header:=xlYes, _
    MatchCase:=False, Orientation:=xlTopToBottom
    End With
    End Sub
    '--
    Jim Cone
    Portland, Oregon USA
    Review: Special Sort add-in

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by Jim Cone View Post
    It added no real value. They have no shame.

    Depends on your point of view. I've seen a fair few posts where people wanted to sort by more than three fields; now they can without doing multiple sorts.

    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •