Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Errors
Help! I can't get a sort to run correctly; both are "sort method of range
class failed"; I'm running this on Excel 2003 This is a macro-based code: Columns("D:D").Select Range("B1:U500").SORT Key1:=Range("D1"), Order1:=xlDescending, Key2:= _ Range("E1"), Order2:=xlAscending, Key3:=Range("G1"), Order3:=xlAscending _ , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _ , DataOption3:=xlSortNormal I also can't get this (preferred) code to work: With ActiveSheet Dim Rng As Range Set Rng = .Range(.Range("U1"), .Cells(Rows.Count, "B").End(xlUp)) Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"), Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending, MatchCase:=False, Header:=xlYes End With Suzanne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Errors
columns U and B are backwards
With ActiveSheet Dim Rng As Range Set Rng = .Range(.Range("B1"), .Cells(Rows.Count, "U").End(xlUp)) Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"), Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending, MatchCase:=False, Header:=xlYes End With "Suzanne" wrote: Help! I can't get a sort to run correctly; both are "sort method of range class failed"; I'm running this on Excel 2003 This is a macro-based code: Columns("D:D").Select Range("B1:U500").SORT Key1:=Range("D1"), Order1:=xlDescending, Key2:= _ Range("E1"), Order2:=xlAscending, Key3:=Range("G1"), Order3:=xlAscending _ , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _ , DataOption3:=xlSortNormal I also can't get this (preferred) code to work: With ActiveSheet Dim Rng As Range Set Rng = .Range(.Range("U1"), .Cells(Rows.Count, "B").End(xlUp)) Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"), Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending, MatchCase:=False, Header:=xlYes End With Suzanne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Errors
There must be something else going on here. I tested your prefered code and
it worked fine by me. The one thing that I notice is that your key work Sort is all caps. Have you declared a procedure or sub or ??? somewhere called SORT. If so then that could be the problem... Sub test() With ActiveSheet Dim Rng As Range Set Rng = .Range(.Range("U1"), .Cells(Rows.Count, "B").End(xlUp)) Rng.Sort Key1:=.Cells(2, "D"), Order1:=xlDescending, _ Key2:=.Cells(2, "E"), Order2:=xlAscending, _ Key3:=.Cells(2, "G"), Order3:=xlAscending, _ MatchCase:=False, Header:=xlYes End With End Sub -- HTH... Jim Thomlinson "Suzanne" wrote: Help! I can't get a sort to run correctly; both are "sort method of range class failed"; I'm running this on Excel 2003 This is a macro-based code: Columns("D:D").Select Range("B1:U500").SORT Key1:=Range("D1"), Order1:=xlDescending, Key2:= _ Range("E1"), Order2:=xlAscending, Key3:=Range("G1"), Order3:=xlAscending _ , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _ , DataOption3:=xlSortNormal I also can't get this (preferred) code to work: With ActiveSheet Dim Rng As Range Set Rng = .Range(.Range("U1"), .Cells(Rows.Count, "B").End(xlUp)) Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"), Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending, MatchCase:=False, Header:=xlYes End With Suzanne |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Errors
The order in which B and U appear makes no difference in terms of creating a
range object. In this case if B is always populated to the end of the data set but U may not have data all the way to the end, the way that the OP has created the code is correct. Yours may end up missing some rows. -- HTH... Jim Thomlinson "Joel" wrote: columns U and B are backwards With ActiveSheet Dim Rng As Range Set Rng = .Range(.Range("B1"), .Cells(Rows.Count, "U").End(xlUp)) Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"), Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending, MatchCase:=False, Header:=xlYes End With "Suzanne" wrote: Help! I can't get a sort to run correctly; both are "sort method of range class failed"; I'm running this on Excel 2003 This is a macro-based code: Columns("D:D").Select Range("B1:U500").SORT Key1:=Range("D1"), Order1:=xlDescending, Key2:= _ Range("E1"), Order2:=xlAscending, Key3:=Range("G1"), Order3:=xlAscending _ , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _ , DataOption3:=xlSortNormal I also can't get this (preferred) code to work: With ActiveSheet Dim Rng As Range Set Rng = .Range(.Range("U1"), .Cells(Rows.Count, "B").End(xlUp)) Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"), Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending, MatchCase:=False, Header:=xlYes End With Suzanne |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Errors
This didn't fix it at all... the command wouldn't even run.
I thought that Range was the last column ("U") and the Rows.Count was the starting point. "Joel" wrote: columns U and B are backwards With ActiveSheet Dim Rng As Range Set Rng = .Range(.Range("B1"), .Cells(Rows.Count, "U").End(xlUp)) Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"), Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending, MatchCase:=False, Header:=xlYes End With "Suzanne" wrote: Help! I can't get a sort to run correctly; both are "sort method of range class failed"; I'm running this on Excel 2003 This is a macro-based code: Columns("D:D").Select Range("B1:U500").SORT Key1:=Range("D1"), Order1:=xlDescending, Key2:= _ Range("E1"), Order2:=xlAscending, Key3:=Range("G1"), Order3:=xlAscending _ , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _ , DataOption3:=xlSortNormal I also can't get this (preferred) code to work: With ActiveSheet Dim Rng As Range Set Rng = .Range(.Range("U1"), .Cells(Rows.Count, "B").End(xlUp)) Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"), Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending, MatchCase:=False, Header:=xlYes End With Suzanne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
Trying to sort by row and getting errors. | New Users to Excel | |||
Sort creating total errors | Excel Discussion (Misc queries) | |||
how do I sort email address, dedupes,check for errors in a docume. | Excel Discussion (Misc queries) | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |