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

 

[Yongle]

Was the range providing custom list x sorted BEFORE being created ?

 

[ricorico]

Was the range providing custom list x sorted BEFORE being created ?

Thx for your reply.
Yes it is.

 

[Fluff]

How about

.AddCustomList Range("P9:P16").Value

 

[ricorico]

How about

.AddCustomList Range("P9:P16").Value

.AddCustomList Range("P9:P16").Value
not work, this line highlighted and stopped.


BTW I use Excel 2016 for Mac

 

[Fluff]

Please define "not work"
Did you get an error if so what was the error number and message?

 

[ricorico]

Please define "not work"
Did you get an error if so what was the error number and message?

Oh! Sorry for that, the msg appeared


Run-time error '1004':
" Method 'AddCustomList' of Object '_Application' failed

 

[Fluff]

Does P9 on the active sheet contain a value?

 

[ricorico]

Does P9 on the active sheet contain a value?

Yes, P9 to P16 contain numbers

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

 

[Fluff]

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.