Solved

Excel 2003 VBA Sort compile error

Posted on 2010-08-12
6
800 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ifna and iferror 1 32
excel pivot question 4 40
Vb.net dynamic formulas in runtime 11 61
Formula 6 45
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

948 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

23 Experts available now in Live!

Get 1:1 Help Now