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

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" wrote in
message ...

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

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 lis
or does it do something different?

Thanks.

Jame

--
fugfu
-----------------------------------------------------------------------
fugfug's Profile: http://www.excelforum.com/member.php...fo&userid=2495
View this thread: http://www.excelforum.com/showthread.php?threadid=38670

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

Jame

--
fugfu
-----------------------------------------------------------------------
fugfug's Profile: http://www.excelforum.com/member.php...fo&userid=2495
View this thread: http://www.excelforum.com/showthread.php?threadid=38670

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

Jame

--
fugfu
-----------------------------------------------------------------------
fugfug's Profile: http://www.excelforum.com/member.php...fo&userid=2495
View this thread: http://www.excelforum.com/showthread.php?threadid=38670

Just for information:

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

--
Regards,
Tom Ogilvy


"fugfug" wrote in
message ...

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

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" wrote in
message ...

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

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

--
fugfu
-----------------------------------------------------------------------
fugfug's Profile: http://www.excelforum.com/member.php...fo&userid=2495
View this thread: http://www.excelforum.com/showthread.php?threadid=38670

If your interested the code I used is as follows, customers is the shee
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 Su

--
fugfu
-----------------------------------------------------------------------
fugfug's Profile: http://www.excelforum.com/member.php...fo&userid=2495
View this thread: http://www.excelforum.com/showthread.php?threadid=38670

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" wrote in
message ...

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