Sort - OrderCustom

Hi programmers,

In Excel 2K
Selection.Sort ... OrderCustom:=xx
seems to work only using the number of a custom list. But how to be sure of
this number on any other computer then your own?
I tried to refere a range on a sheet, to create and use a (matrix) variable,
.... No go.
The only way seems to be to add the custom list, find the number and use it.

A kiss for a smart solution ;-).

Frans

Basically, you have it right, you need to add it, then sort on the new
number

OrderCustom:=Application.CustomListCount

But guess what, there is a problem. See this article

http://support.microsoft.com/default.aspx?kbid=134913
XL: GetCustomListNum Returns Unexpected List Number

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Frans van Zelm" <fhvzelm@hotmail.com> wrote in message
news:%23hUj4tIlFHA.3148@TK2MSFTNGP09.phx.gbl...
> Hi programmers,
>
> In Excel 2K
> Selection.Sort ... OrderCustom:=xx
> seems to work only using the number of a custom list. But how to be sure
of
> this number on any other computer then your own?
> I tried to refere a range on a sheet, to create and use a (matrix)
variable,
> ... No go.
> The only way seems to be to add the custom list, find the number and use
it.
>
> A kiss for a smart solution ;-).
>
> Frans
>
>

Hi Frans,

Adding your list is subject to a problem if the lists aleady exists.

According to VBA help:

"If the list you're trying to add already exists, this method does nothing".

However, my experience is that attempting to add a list that aleady exists,
causes a 1004 run-time error.

In either event, attempting to add a list will fail if the list already
exists.

A workaround, that works for me, is to include a spurious entry as the
first item of the list. This way the new list will always be accepted and
the list's postion will always be after any existing lists, i.e the new list
will always be in position: Application.CustomListCount.

Unlike the GetCustomListNum method, I am not aware of any problem with use
of the CustomListCount property.

If the spurious item is chosen such that it will not be found in any data to
be sorted, it should have no adverse impact.


---
Regards,
Norman


"Frans van Zelm" <fhvzelm@hotmail.com> wrote in message
news:%23hUj4tIlFHA.3148@TK2MSFTNGP09.phx.gbl...
> Hi programmers,
>
> In Excel 2K
> Selection.Sort ... OrderCustom:=xx
> seems to work only using the number of a custom list. But how to be sure
> of
> this number on any other computer then your own?
> I tried to refere a range on a sheet, to create and use a (matrix)
> variable,
> ... No go.
> The only way seems to be to add the custom list, find the number and use
> it.
>
> A kiss for a smart solution ;-).
>
> Frans
>
>

Hi Franz,

Retesting under xl2000, I was able to reproduce the problem mentioned in
Bob's MSKB link with a sort expression like:

OrderCustom:=Application.CustomListCount

Using the KB suggestion, and replacing the sort expression with:

OrderCustom:=Application.CustomListCount +1

resolved matters however.

---
Regards,
Norman



"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:uq8znmJlFHA.1948@TK2MSFTNGP12.phx.gbl...
> Hi Frans,
>
> Adding your list is subject to a problem if the lists aleady exists.
>
> According to VBA help:
>
> "If the list you're trying to add already exists, this method does
> nothing".
>
> However, my experience is that attempting to add a list that aleady
> exists, causes a 1004 run-time error.
>
> In either event, attempting to add a list will fail if the list already
> exists.
>
> A workaround, that works for me, is to include a spurious entry as the
> first item of the list. This way the new list will always be accepted and
> the list's postion will always be after any existing lists, i.e the new
> list will always be in position: Application.CustomListCount.
>
> Unlike the GetCustomListNum method, I am not aware of any problem with use
> of the CustomListCount property.
>
> If the spurious item is chosen such that it will not be found in any data
> to be sorted, it should have no adverse impact.
>
>
> ---
> Regards,
> Norman
>
>
> "Frans van Zelm" <fhvzelm@hotmail.com> wrote in message
> news:%23hUj4tIlFHA.3148@TK2MSFTNGP09.phx.gbl...
>> Hi programmers,
>>
>> In Excel 2K
>> Selection.Sort ... OrderCustom:=xx
>> seems to work only using the number of a custom list. But how to be sure
>> of
>> this number on any other computer then your own?
>> I tried to refere a range on a sheet, to create and use a (matrix)
>> variable,
>> ... No go.
>> The only way seems to be to add the custom list, find the number and use
>> it.
>>
>> A kiss for a smart solution ;-).
>>
>> Frans
>>
>>
>
>

Hi Norman,

the problem with this is that it is not consistent, so do you add or not?

Bob


"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:OxWl8zJlFHA.764@TK2MSFTNGP14.phx.gbl...
> Hi Franz,
>
> Retesting under xl2000, I was able to reproduce the problem mentioned in
> Bob's MSKB link with a sort expression like:
>
> OrderCustom:=Application.CustomListCount
>
> Using the KB suggestion, and replacing the sort expression with:
>
> OrderCustom:=Application.CustomListCount +1
>
> resolved matters however.
>
> ---
> Regards,
> Norman
>
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:uq8znmJlFHA.1948@TK2MSFTNGP12.phx.gbl...
> > Hi Frans,
> >
> > Adding your list is subject to a problem if the lists aleady exists.
> >
> > According to VBA help:
> >
> > "If the list you're trying to add already exists, this method does
> > nothing".
> >
> > However, my experience is that attempting to add a list that aleady
> > exists, causes a 1004 run-time error.
> >
> > In either event, attempting to add a list will fail if the list already
> > exists.
> >
> > A workaround, that works for me, is to include a spurious entry as the
> > first item of the list. This way the new list will always be accepted
and
> > the list's postion will always be after any existing lists, i.e the new
> > list will always be in position: Application.CustomListCount.
> >
> > Unlike the GetCustomListNum method, I am not aware of any problem with
use
> > of the CustomListCount property.
> >
> > If the spurious item is chosen such that it will not be found in any
data
> > to be sorted, it should have no adverse impact.
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> > "Frans van Zelm" <fhvzelm@hotmail.com> wrote in message
> > news:%23hUj4tIlFHA.3148@TK2MSFTNGP09.phx.gbl...
> >> Hi programmers,
> >>
> >> In Excel 2K
> >> Selection.Sort ... OrderCustom:=xx
> >> seems to work only using the number of a custom list. But how to be
sure
> >> of
> >> this number on any other computer then your own?
> >> I tried to refere a range on a sheet, to create and use a (matrix)
> >> variable,
> >> ... No go.
> >> The only way seems to be to add the custom list, find the number and
use
> >> it.
> >>
> >> A kiss for a smart solution ;-).
> >>
> >> Frans
> >>
> >>
> >
> >
>
>

Hi Bob,

Using Application.CustomListCount +1 in the Sort statement, seemed to work
consistently for me - but I cannot vouch for this as a universal solution.


---
Regards,
Norman



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OJHxYNTlFHA.3568@tk2msftngp13.phx.gbl...
> Hi Norman,
>
> the problem with this is that it is not consistent, so do you add or not?
>
> Bob

I first used CustomListCount in a solution I provided, and I did not have to
add 1. So I was surprised when in another response I gave, the OP responded
with a problem which we traced to this inconsistency. So, I have never seen
it, you seem to always see it, and I hate inconsistency :-).

Regards

Bob


"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:%23W68lfTlFHA.2156@TK2MSFTNGP14.phx.gbl...
> Hi Bob,
>
> Using Application.CustomListCount +1 in the Sort statement, seemed to work
> consistently for me - but I cannot vouch for this as a universal solution.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OJHxYNTlFHA.3568@tk2msftngp13.phx.gbl...
> > Hi Norman,
> >
> > the problem with this is that it is not consistent, so do you add or
not?
> >
> > Bob
>
>
>