Custom sort issue

[ricorico]

Here is my code :

Sub SortCol()

With Application
.AddCustomList listArray:=Array("P9:P16")
x = .CustomListCount
Range("A9:N16").Sort Key1:=Range("A9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=x, MatchCase:=False, Orientation:=xlTopToBottom
.DeleteCustomList ListNum:=x + 1
End With
End Sub

Cell range A9:N16 are the data to be sorted. P9:P16 is a custom list containing number (i.e. 1 ,2 ,3 ,5 ,7 ,9 ,8 ,6).
The code doesn't work , any idea?


from KH

 

[ricorico]

Yes, P9 to P16 contain numbers

P9 P10 P11 P12 P13 P14 P15 P16
3 4 6 7 8 2 1 5

 

[ricorico]

In that case it maybe you can't do that on a Mac & as I don't use Mac's I cannot help any further.

You helped a lot, Thx much!

 

[Fluff]

You're welcome

 

[ricorico]

You're welcome

I use this code work fine.

Sub SortCol()

With Application
.AddCustomList ListArray:=Array("3", "4", "6", "7", "8", "2", "1", "5", """""")
x = .CustomListCount
Range("A9:N16").Sort Key1:=Range("A9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=x, MatchCase:=False, Orientation:=xlSortColumns
.DeleteCustomList ListNum:=x + 1
End With

End Sub


But don't know how to change " Array("3", "4", "6", "7", "8", "2", "1", "5", """""")" to a range (P9:P16)

 

[Yongle]

Try this method to create the custom list
- it works for me but unfortunately for you I am not a Mac user either

    Dim m As String, c As Range
    With Application
        For Each c In Range("P9:P16")
            m = m & c & ", "
        Next c
        m = Left(m, Len(m) - 2)
        .AddCustomList ListArray:=Array(m)
    End With

 

[ricorico]

Try this method to create the custom list
- it works for me but unfortunately for you I am not a Mac user either

    Dim m As String, c As Range
    With Application
        For Each c In Range("P9:P16")
            m = m & c & ", "
        Next c
        m = Left(m, Len(m) - 2)
        .AddCustomList ListArray:=Array(m)
    End With

Thx for your reply, I finally got it to work by modify my original code.

I will try your code too , Thx!