Sorting with Custom List

[Mike2]

Hello,

I am looking to have two sorting done with my code. First order is from Custom List and the 2nd order is Smallest to Largest.

Here is my code:

Dim sStatus As Range
Dim sVarValue As Range

Application.AddCustomList ListArray:=Array("0 Count","Shortage","Overage")

Set sStatus = Rows(1).Find("Status")
Set sVarValue=Rows(1).Find("Variance $")

ActiveSheet.Sort.SortFields.Clear

Range("A1").CurrentRegion.Resize(Range("A1").CurrentRegion.Rows.Count - 1).Sort Key1:=sStatus, Order1:=xlAscending, Ordercustom:=Application.CustomListCount, _
        Key2:=sVarValue, Order2:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

The result of the Code does not sort the way i wanted to be.

I want the result of the sorting to be as following:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Variance $[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD](8000)[/TD]
[TD]0 Count[/TD]
[/TR]
[TR]
[TD](5000)[/TD]
[TD]0 Count[/TD]
[/TR]
[TR]
[TD](4000)[/TD]
[TD]0 Count[/TD]
[/TR]
[TR]
[TD](1200)[/TD]
[TD]Shortage[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Overage[/TD]
[/TR]
[TR]
[TD]1200[/TD]
[TD]Overage[/TD]
[/TR]
</tbody>[/TABLE]

Any help from your expertise is much appreciated...thank you

 

[higrm]

https://www.mrexcel.com/forum/excel-questions/966034-vba-custom-sort-usedrange.html

I think this is what you are looking for.

 

[Fluff]

Try

Dim sStatus As Range
Dim sVarValue As Range

Application.AddCustomList ListArray:=Array("0 Count", "Shortage", "Overage")

Set sStatus = Rows(1).Find("Status")
Set sVarValue = Rows(1).Find("Variance $")

ActiveSheet.Sort.SortFields.Clear

Range("A1").CurrentRegion.Sort Key1:=sStatus, Order1:=xlAscending, Ordercustom:=Application.CustomListCount + 1, _
        Key2:=sVarValue, Order2:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

 

[Mike2]

Thank you higrm and Fluff for your quick respond.
@Fluff, your suggestion with +1 at the end of CustomListCount works.
But how does it calculate ? I tried looking up at the Microsoft reference site, but it does not give much detail explanation...thank you

 

[Fluff]

The count for CustomList starts at zero, so to get the last sort in the list you need to add 1 to the count.

 

[Mike2]

Thank you for the explanation Fluff.

 

[Fluff]

You're welcome