Solved

Excel 2003 VBA Sort compile error

Posted on 2010-08-12
6
798 Views
Last Modified: 2012-08-13
Hi,

Would anyone know why the attached code is causing a compile error. The error I'm getting highlights the 'Sort' syntax in the ws3.Sort.SortFields.Clear line and returns a 'Method or data member not found' error box.

This code works fine in Excel 2007 but fails in 2003.

Thanks
Toco

Set ws3 = ThisWorkbook.Sheets("Statement")

' clear sort fields on statement

    ws3.Sort.SortFields.Clear

' sort statement by date 

    ws3.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ws3.Sort

        .SetRange Range("A5:E" & ws3.Range("A65536").End(xlUp).Row)

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

Open in new window

0
Comment
Question by:Tocogroup
  • 2
  • 2
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33422215
2003 doesn't like the column ranges .. try something like replacing

    ws3.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

with
    ws3.Sort.SortFields.Add Key:=Range("A1:A" & ws3.rows.count), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal


Chris
0
 

Author Comment

by:Tocogroup
ID: 33422299
No, that didn't work either.....same error message as before against the Clear Sortfields line.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33422301
>>This code works fine in Excel 2007 but fails in 2003.That whole Sort object is new to Excel 2007, and will not work in earlier versions.  To sort in Excel 2003 and earlier you need to use the old Sort method of the Range object.  See below.
Set ws3 = ThisWorkbook.Sheets("Statement")

 

With ws3

    .Range("A5:E" & .Cells(.Rows.Count, 1).End(xlUp).Row).Sort Key1:=.[a5], _

        Order1:=xlAscending, Header:=xlYes

    

    ' if you do not have a header...

    ' .Range("A5:E" & .Cells(.Rows.Count, 1).End(xlUp).Row).Sort Key1:=.[a5], _

    '     Order1:=xlAscending, Header:=xlNo

End With

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Closing Comment

by:Tocogroup
ID: 33422427
Many thanks.
That works great.
Toco
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33422443
Small amount of knowledge = danger ... sorry, I assumed too much and hadn't realised the functionality was new (I almost always start out by recording the function and then removing teh surplus code).

Chris
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33422687
:) to ChrisThat old Sort method still works in the newer versions of Excel, so I still use it whenever I only need to sort on 1-3 keys :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now