[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Excel 2003 VBA Sort compile error

Posted on 2010-08-12
6
Medium Priority
?
846 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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 93

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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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 …

607 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