Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort
Hi,
XL2003 I was hoping someone could shed some light on this for me. In the following code, notice that I had to use "n+1" to make it work when sorting, but just n to delete the custom list. When I look at the custom list in Tools,Options, I see: NEW LIST Mon, Tue, Wed, etc Monday, Tuesday, Wednesday, etc Jan, Feb, Mar, etc January, February, March, etc For some reason, when actually using the custom list, XL seems to be counting the "NEW LIST" as number 1, but not so when deleting or when getting the custom list number. Application.AddCustomList Array("T", "B", "E") n = Application.GetCustomListNum(Array("T", "B", "E")) Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustom:=n + 1 Application.DeleteCustomList n Is there a way to sort, according to a custom list, without actually creating an official custom list? I tried: Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustomArray("T", "B", "E") but it gave an error. Regards - Dave. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort
Arrays are zero based by default, so when you use OrderCustom:=n it sees a
zero. You can declare it as a base 1 array, but it is just as easy to do what you did and add 1 where necessary. "Dave" wrote: Hi, XL2003 I was hoping someone could shed some light on this for me. In the following code, notice that I had to use "n+1" to make it work when sorting, but just n to delete the custom list. When I look at the custom list in Tools,Options, I see: NEW LIST Mon, Tue, Wed, etc Monday, Tuesday, Wednesday, etc Jan, Feb, Mar, etc January, February, March, etc For some reason, when actually using the custom list, XL seems to be counting the "NEW LIST" as number 1, but not so when deleting or when getting the custom list number. Application.AddCustomList Array("T", "B", "E") n = Application.GetCustomListNum(Array("T", "B", "E")) Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustom:=n + 1 Application.DeleteCustomList n Is there a way to sort, according to a custom list, without actually creating an official custom list? I tried: Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustomArray("T", "B", "E") but it gave an error. Regards - Dave. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort
Hi,
Ok, but it still seems inconsistent to me. When I use the n=GetCustomListNum then n=5 When I do the sort, OrderCustom:= has to be 6 (n+1) When I DeleteCustomList, I have to use n (5) And can you help with the 2nd question regarding declaring simple lists within the sort method? Regards - Dave. "JLGWhiz" wrote: Arrays are zero based by default, so when you use OrderCustom:=n it sees a zero. You can declare it as a base 1 array, but it is just as easy to do what you did and add 1 where necessary. "Dave" wrote: Hi, XL2003 I was hoping someone could shed some light on this for me. In the following code, notice that I had to use "n+1" to make it work when sorting, but just n to delete the custom list. When I look at the custom list in Tools,Options, I see: NEW LIST Mon, Tue, Wed, etc Monday, Tuesday, Wednesday, etc Jan, Feb, Mar, etc January, February, March, etc For some reason, when actually using the custom list, XL seems to be counting the "NEW LIST" as number 1, but not so when deleting or when getting the custom list number. Application.AddCustomList Array("T", "B", "E") n = Application.GetCustomListNum(Array("T", "B", "E")) Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustom:=n + 1 Application.DeleteCustomList n Is there a way to sort, according to a custom list, without actually creating an official custom list? I tried: Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustomArray("T", "B", "E") but it gave an error. Regards - Dave. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort
I think that if you check the value of n immediately after the sort line is
executed, you will find that n = 1. That tells it the order to sort in. In the VBA help file it states that CustomOrder value is 1-based. which means, of course, that value must be at least 1. As for the n(5), that is the expression for the maximum size of your array. Until you loop through your array, the first use of n will have a value of n = 0 which is the LBound (lower boundary) and if you fully load the array then the UBound (upper boundary) of the array would be 5 for a total of six items. You can look this all up in the VBA help files for more details. "Dave" wrote: Hi, Ok, but it still seems inconsistent to me. When I use the n=GetCustomListNum then n=5 When I do the sort, OrderCustom:= has to be 6 (n+1) When I DeleteCustomList, I have to use n (5) And can you help with the 2nd question regarding declaring simple lists within the sort method? Regards - Dave. "JLGWhiz" wrote: Arrays are zero based by default, so when you use OrderCustom:=n it sees a zero. You can declare it as a base 1 array, but it is just as easy to do what you did and add 1 where necessary. "Dave" wrote: Hi, XL2003 I was hoping someone could shed some light on this for me. In the following code, notice that I had to use "n+1" to make it work when sorting, but just n to delete the custom list. When I look at the custom list in Tools,Options, I see: NEW LIST Mon, Tue, Wed, etc Monday, Tuesday, Wednesday, etc Jan, Feb, Mar, etc January, February, March, etc For some reason, when actually using the custom list, XL seems to be counting the "NEW LIST" as number 1, but not so when deleting or when getting the custom list number. Application.AddCustomList Array("T", "B", "E") n = Application.GetCustomListNum(Array("T", "B", "E")) Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustom:=n + 1 Application.DeleteCustomList n Is there a way to sort, according to a custom list, without actually creating an official custom list? I tried: Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustomArray("T", "B", "E") but it gave an error. Regards - Dave. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort
It seems weird to me, too.
Without knowing the real reason, I think it's because two different programmers (or two different teams of programmers or even one person at different times!) didn't really talk to each other. VBA's help (xl2003) for Sort shows this for ordercustom: OrderCustom Optional Variant. This argument is a one-based integer offset to the list of custom sort orders. If you omit OrderCustom, a normal sort is used. But I think that this just documents how it was implemented. My guess (and it's just a guess) is that the sort programmer expected the custom list programmer to use 0 based stuff. And the sort programmer wanted to index starting at 0+1, but then saw how his portion had to work. It was too late in the development cycle (or too many versions of excel were already in used) to go back and "fix" the problem. So the solution was to document it in the Help. And that made it a documented feature, not a problem/bug/error. But that's just a guess! And you could go to some trouble by building a two column table -- on column for the value and one for the order. Then use =vlookup() to return that order and sort by the helper column. But that's way too much work! And since it's something I never remember (and I bet you won't either <bg), document your code so that if you need to do it again and find this macro, you won't have to scratch your head again (or test until you see the solution!). Dave wrote: Hi, XL2003 I was hoping someone could shed some light on this for me. In the following code, notice that I had to use "n+1" to make it work when sorting, but just n to delete the custom list. When I look at the custom list in Tools,Options, I see: NEW LIST Mon, Tue, Wed, etc Monday, Tuesday, Wednesday, etc Jan, Feb, Mar, etc January, February, March, etc For some reason, when actually using the custom list, XL seems to be counting the "NEW LIST" as number 1, but not so when deleting or when getting the custom list number. Application.AddCustomList Array("T", "B", "E") n = Application.GetCustomListNum(Array("T", "B", "E")) Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustom:=n + 1 Application.DeleteCustomList n Is there a way to sort, according to a custom list, without actually creating an official custom list? I tried: Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustomArray("T", "B", "E") but it gave an error. Regards - Dave. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort
Dave,
The "Normal" sort has an index (value) of 1. The custom sort indexes are added to that value. (and yes, 0 for the normal sort would have made more sense) -- Jim Cone Portland, Oregon USA "Dave Peterson" wrote in message It seems weird to me, too. Without knowing the real reason, I think it's because two different programmers (or two different teams of programmers or even one person at different times!) didn't really talk to each other. VBA's help (xl2003) for Sort shows this for ordercustom: OrderCustom Optional Variant. This argument is a one-based integer offset to the list of custom sort orders. If you omit OrderCustom, a normal sort is used. But I think that this just documents how it was implemented. My guess (and it's just a guess) is that the sort programmer expected the custom list programmer to use 0 based stuff. And the sort programmer wanted to index starting at 0+1, but then saw how his portion had to work. It was too late in the development cycle (or too many versions of excel were already in used) to go back and "fix" the problem. So the solution was to document it in the Help. And that made it a documented feature, not a problem/bug/error. But that's just a guess! And you could go to some trouble by building a two column table -- on column for the value and one for the order. Then use =vlookup() to return that order and sort by the helper column. But that's way too much work! And since it's something I never remember (and I bet you won't either <bg), document your code so that if you need to do it again and find this macro, you won't have to scratch your head again (or test until you see the solution!). Dave wrote: Hi, XL2003 I was hoping someone could shed some light on this for me. In the following code, notice that I had to use "n+1" to make it work when sorting, but just n to delete the custom list. When I look at the custom list in Tools,Options, I see: NEW LIST Mon, Tue, Wed, etc Monday, Tuesday, Wednesday, etc Jan, Feb, Mar, etc January, February, March, etc For some reason, when actually using the custom list, XL seems to be counting the "NEW LIST" as number 1, but not so when deleting or when getting the custom list number. Application.AddCustomList Array("T", "B", "E") n = Application.GetCustomListNum(Array("T", "B", "E")) Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustom:=n + 1 Application.DeleteCustomList n Is there a way to sort, according to a custom list, without actually creating an official custom list? I tried: Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustomArray("T", "B", "E") but it gave an error. Regards - Dave. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort
Hi Jim,
Ok, that makes better sense. Thanks for taking the time. And yes, I will make a not in my code. Regards - Dave. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort
Hi Dave,
Sorry, answered the wrong post. Please see answer to Jim Cone's post, and take it as yours. Dave. "Dave Peterson" wrote: It seems weird to me, too. Without knowing the real reason, I think it's because two different programmers (or two different teams of programmers or even one person at different times!) didn't really talk to each other. VBA's help (xl2003) for Sort shows this for ordercustom: OrderCustom Optional Variant. This argument is a one-based integer offset to the list of custom sort orders. If you omit OrderCustom, a normal sort is used. But I think that this just documents how it was implemented. My guess (and it's just a guess) is that the sort programmer expected the custom list programmer to use 0 based stuff. And the sort programmer wanted to index starting at 0+1, but then saw how his portion had to work. It was too late in the development cycle (or too many versions of excel were already in used) to go back and "fix" the problem. So the solution was to document it in the Help. And that made it a documented feature, not a problem/bug/error. But that's just a guess! And you could go to some trouble by building a two column table -- on column for the value and one for the order. Then use =vlookup() to return that order and sort by the helper column. But that's way too much work! And since it's something I never remember (and I bet you won't either <bg), document your code so that if you need to do it again and find this macro, you won't have to scratch your head again (or test until you see the solution!). Dave wrote: Hi, XL2003 I was hoping someone could shed some light on this for me. In the following code, notice that I had to use "n+1" to make it work when sorting, but just n to delete the custom list. When I look at the custom list in Tools,Options, I see: NEW LIST Mon, Tue, Wed, etc Monday, Tuesday, Wednesday, etc Jan, Feb, Mar, etc January, February, March, etc For some reason, when actually using the custom list, XL seems to be counting the "NEW LIST" as number 1, but not so when deleting or when getting the custom list number. Application.AddCustomList Array("T", "B", "E") n = Application.GetCustomListNum(Array("T", "B", "E")) Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustom:=n + 1 Application.DeleteCustomList n Is there a way to sort, according to a custom list, without actually creating an official custom list? I tried: Range(z).Sort _ Key1:=Range("C2"), Header:=xlNo, OrderCustomArray("T", "B", "E") but it gave an error. Regards - Dave. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom sort by vba | Excel Programming | |||
Custom Sort | Excel Worksheet Functions | |||
Custom Sort | Excel Discussion (Misc queries) | |||
Custom Sort | Excel Programming | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) |