Tocogroup
asked on
Excel 2003 VBA Sort compile error
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
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
ASKER
No, that didn't work either.....same error message as before against the Clear Sortfields line.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks.
That works great.
Toco
That works great.
Toco
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
Chris
:) 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 :)
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