Solved

Excel 2003 VBA Sort compile error

Posted on 2010-08-12
6
804 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

860 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