Code for Advanced Sort

[fugfug]

Hi,

I am trying to write a macro to do a custom sort. I have writen part of it which enters a custom list then it moves to the data I want to sort and it is supposed to sort it in the order of the custom list. My problem is I can't get the macro to choose my custom list which is at the bottom of the custom lists. Below is the code.

Sheets("Customers").Select
Range("A3").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=12, MatchCase:=False, Orientation:=xlTopToBottom

Thanks.

nb. Using Excel 97

James

[Bob Phillips]

If you are creating the customlist on the fly, it will get added to the end.

Sheets("Customers").Select
Range("A3").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=Application.CustomListCount, MatchCase:=False,
Orientation:=xlTopToBottom

I advise deleting it a=fterwards as well

Application.DeleteCustomList Application.CustomListCount

--
HTH

Bob Phillips

"fugfug" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I am trying to write a macro to do a custom sort. I have writen part of
> it which enters a custom list then it moves to the data I want to sort
> and it is supposed to sort it in the order of the custom list. My
> problem is I can't get the macro to choose my custom list which is at
> the bottom of the custom lists. Below is the code.
>
> Sheets("Customers").Select
> Range("A3").Select
> Selection.Sort Key1:=Range("A3"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=12, MatchCase:=False, Orientation:=xlTopToBottom
>
> Thanks.
>
> nb. Using Excel 97
>
> James
>
>
> --
> fugfug
> ------------------------------------------------------------------------
> fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
> View this thread: http://www.excelforum.com/showthread...hreadid=386709
>

[fugfug]

Thanks Bob. I see the bit you have changed in the code

OrderCustom:=Application.CustomListCount

In my code I had this = to 12 which I assume means twelth on the list. Does the customlistcount just count how many items in the custom list or does it do something different?

Thanks.

James

[fugfug]

I've just tried the new code, it does not quite work. It always chooses the custom list one from the bottom of the list........

James

[fugfug]

I've changed it to OrderCustom:=(Application.CustomListCount) + 1 and it seems to work, still a bit confused as to what customlistcount does.

James

[Tom Ogilvy]

Just for information:

http://support.microsoft.com/default...b;en-us;134913
XL: GetCustomListNum Returns Unexpected List Number

--
Regards,
Tom Ogilvy


"fugfug" <[email protected]> wrote in
message news:[email protected]...
>
> I've changed it to OrderCustom:=(Application.CustomListCount) + 1 and it
> seems to work, still a bit confused as to what customlistcount does.
>
> James
>
>
> --
> fugfug
> ------------------------------------------------------------------------
> fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
> View this thread: http://www.excelforum.com/showthread...hreadid=386709
>

[Bob Phillips]

James,

I have used custom lists in VBA a bit but never come across this. Your
workaround is confirmed by Tom, but it seems a bit flaky. Did you create the
custom list just prior to sorting, or was it already present?

--
HTH

Bob Phillips

"fugfug" <[email protected]> wrote in
message news:[email protected]...
>
> I've changed it to OrderCustom:=(Application.CustomListCount) + 1 and it
> seems to work, still a bit confused as to what customlistcount does.
>
> James
>
>
> --
> fugfug
> ------------------------------------------------------------------------
> fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
> View this thread: http://www.excelforum.com/showthread...hreadid=386709
>

[fugfug]

Yep, just prior to sorting. The code I posted is the final part of a macro which also includes the creation of the custom list. I tested the final bit of code without the rest of macro aswell but the same thing still happens. It works fine now with the +1 but it is an interesting qwerk!

[fugfug]

If your interested the code I used is as follows, customers is the sheet with the data to be sorted on.

Sub List()
'
' List Macro
' Macro recorded 12/07/2005 by James Fuggle
Sheets.Add
ActiveSheet.Select
ActiveSheet.Name = "List"
Range("A1").Select

Dim reply As String
Do Until reply = "stop"
ActiveCell.Offset(1, 0).Select


reply = InputBox("Enter company name", "Company Input")
ActiveCell.FormulaR1C1 = reply


Loop
ActiveCell.ClearContents

Application.AddCustomList ListArray:=Range("A1:A2000")

Sheets("Customers").Select
Range("A3:N2000").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=(Application.CustomListCount) + 1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

[Bob Phillips]

Thanks for that.

The thing that worries me is that I haven't experienced it as I said, so
that means it is intermittent. Thus by adding 1, it may one day fail with
that. The only way I can see is to test it with the index before using in
anger - nasty!

--
HTH

Bob Phillips

"fugfug" <[email protected]> wrote in
message news:[email protected]...
>
> If your interested the code I used is as follows, customers is the sheet
> with the data to be sorted on.
>
> Sub List()
> '
> ' List Macro
> ' Macro recorded 12/07/2005 by James Fuggle
> Sheets.Add
> ActiveSheet.Select
> ActiveSheet.Name = "List"
> Range("A1").Select
>
> Dim reply As String
> Do Until reply = "stop"
> ActiveCell.Offset(1, 0).Select
>
>
> reply = InputBox("Enter company name", "Company Input")
> ActiveCell.FormulaR1C1 = reply
>
>
> Loop
> ActiveCell.ClearContents
>
> Application.AddCustomList ListArray:=Range("A1:A2000")
>
> Sheets("Customers").Select
> Range("A3:N2000").Select
> Selection.Sort Key1:=Range("A3"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=(Application.CustomListCount) + 1, MatchCase:=False,
> Orientation:=xlTopToBottom
>
> End Sub
>
>
> --
> fugfug
> ------------------------------------------------------------------------
> fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
> View this thread: http://www.excelforum.com/showthread...hreadid=386709
>