Runtime error 1004 Application-defined or object defined error"on data sort
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
|