Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort error?
I don't know what the difference is between these 2 pieces of code. The
first piece works but the second is giving the error message of "Error: 1004 - The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By Box isn't the same or blank". First piece of coding: xl_WksheetData.Range("A1").Select Range("A1:P" & CStr(intLastRow)).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _ ("E2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal 2nd piece of coding: xl_WksheetData.Range("A1").Select xl_WksheetData.Range("A1:P" & CStr(intLastRow)).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _ ("E2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort error?
The two pieces of code are essentially identical. The only difference is your
reference to the sheet in your sort, which is completely unnecessary. When the sheet is omitted as in your first piece of code the default value is activesheet. Since you did a select immediately prior we know that xl _WksheetData is the active sheet. To that end my guess is that there is more to this than you have posted. If I were writing that piece of code here is what I would have... With xl_WksheetData .Range(.Range("A1"), .Cells(.Rows.Count, "P").End(xlUp)).Sort _ Key1:=.Range("C2"), Order1:=xlAscending, _ Key2:=.Range("E2"), Order2:=xlAscending, _ Key3:=.Range("D2"), Order3:=xlAscending, _ Header:=xlYes End With A few things to note. I got ride of many of the sort arguments as they are not necessary. I changed the header from xlGuess to xlYes. I got rid of the intLastRow as you probably don't need it unless you are using it other places. I also qualified all of the ranges (note the dot in front of the word range) which means that every range is explicitly from the sheet xl_WksheetData. That means that I can run this code even if xl_WksheetData is not the active sheet. -- HTH... Jim Thomlinson "mc" wrote: I don't know what the difference is between these 2 pieces of code. The first piece works but the second is giving the error message of "Error: 1004 - The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By Box isn't the same or blank". First piece of coding: xl_WksheetData.Range("A1").Select Range("A1:P" & CStr(intLastRow)).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _ ("E2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal 2nd piece of coding: xl_WksheetData.Range("A1").Select xl_WksheetData.Range("A1:P" & CStr(intLastRow)).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _ ("E2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort error?
Thanks, it worked.
"Jim Thomlinson" wrote: The two pieces of code are essentially identical. The only difference is your reference to the sheet in your sort, which is completely unnecessary. When the sheet is omitted as in your first piece of code the default value is activesheet. Since you did a select immediately prior we know that xl _WksheetData is the active sheet. To that end my guess is that there is more to this than you have posted. If I were writing that piece of code here is what I would have... With xl_WksheetData .Range(.Range("A1"), .Cells(.Rows.Count, "P").End(xlUp)).Sort _ Key1:=.Range("C2"), Order1:=xlAscending, _ Key2:=.Range("E2"), Order2:=xlAscending, _ Key3:=.Range("D2"), Order3:=xlAscending, _ Header:=xlYes End With A few things to note. I got ride of many of the sort arguments as they are not necessary. I changed the header from xlGuess to xlYes. I got rid of the intLastRow as you probably don't need it unless you are using it other places. I also qualified all of the ranges (note the dot in front of the word range) which means that every range is explicitly from the sheet xl_WksheetData. That means that I can run this code even if xl_WksheetData is not the active sheet. -- HTH... Jim Thomlinson "mc" wrote: I don't know what the difference is between these 2 pieces of code. The first piece works but the second is giving the error message of "Error: 1004 - The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By Box isn't the same or blank". First piece of coding: xl_WksheetData.Range("A1").Select Range("A1:P" & CStr(intLastRow)).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _ ("E2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal 2nd piece of coding: xl_WksheetData.Range("A1").Select xl_WksheetData.Range("A1:P" & CStr(intLastRow)).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _ ("E2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bubble sort Error (Compile Error: Type Mismtach) | Excel Programming | |||
vlookup and sort error | Excel Worksheet Functions | |||
Sort error | Excel Discussion (Misc queries) | |||
Sort Error | Excel Programming | |||
Selection.Sort error | Excel Programming |