Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Runtime error 1004 Application-defined or object defined error" on data sort
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 has 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") to Range("K12") without receiving an error message, so it can't be down to the format of the data in column K. Any help would be appreciated. Thanks Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error 1004- application defined or object defined error | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined error | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming |