Solved

Excel 2003 VBA Sort compile error

Posted on 2010-08-12
6
823 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

628 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