Runtime error 1004 Application-defined or object defined error" on data sort

[csacs]

Hi,

I receive the error message mentioned above using the following macro:

Application.Goto Reference:="Data"
Selection.Sort Key1:=Range("P12"), Order1:=xlDescending
Key2:=Range("B12"), Order2:=xlAscending, Key3:=Range("M12")
Order3:=xlAscending, Key4:=Range("K12"), Order4:=xlAscending
Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I do not receive any error message with the following macro (which ha
one less sort key):

Application.Goto Reference:="Data"
Selection.Sort Key1:=Range("P12"), Order1:=xlDescending
Key2:=Range("B12"), Order2:=xlAscending, Key3:=Range("M12")
Order3:=xlAscending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I can interchange Key3 in the above macro from Range("M12") t
Range("K12") without receiving an error message, so it can't be down t
the format of the data in column K.

Any help would be appreciated.

Thanks

Chri

 

[Ben McClave]

Chris,

The Range.Sort method is limited to three key fields, hence the error. If you have Excel 2007 or later, try the code called "SortXL2007" below. If you are using 2003, try the other code.

Regards,

Ben

Sub SortXL2007()
Dim lFirstRow As Long
Dim lLastRow As Long
Dim vCells() As String

vCells() = Split(Range("Data").Address, ":")
lFirstRow = Range(vCells(0)).Row
lLastRow = Range(vCells(1)).Row

With ActiveWorkbook.Worksheets("Sheet1").sort
.SortFields.Clear
.SortFields.Add _
Key:=Range("$P$" & lFirstRow & ":$P$" & lLastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending _
, DataOption:=xlSortNormal
.SortFields.Add _
Key:=Range("$B$" & lFirstRow & ":$B$" & lLastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
.SortFields.Add _
Key:=Range("$M$" & lFirstRow & ":$M$" & lLastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
.SortFields.Add _
Key:=Range("$K$" & lFirstRow & ":$K$" & lLastRow), _
SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
.SetRange Range("Data")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

Sub SortXL2003()

With Range("Data")
.sort Key1:=Range("K12"), Order1:=xlAscending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

.sort Key1:=Range("P12"), Order1:=xlDescending, _
Key2:=Range("B12"), Order2:=xlAscending, Key3:=Range("M12"), _
Order3:=xlAscending, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

End Sub