Sort condition required

[dlee83]

Hello,

Any help is appreciated.

I have a simple sort function sub that works for a set order using custom sort. Fine

Range("V3:AE52").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 key:=Range( _
"x2:x52"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
Application.CustomListCount, _
DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("v2:ae52")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("a1").Select

I can add the customer sort order after CustomOrder:= which works, or add an application with the array, which is what's above.
Now what I want to do is determine the sort order depending on the value of cell "v3"

Just for simplicity on a solution:
If cell value is "A" - Sort order needs to be "B", "C", "D", "E", "F", "A"
If cell value is "B" - Sort order needs to be "C", "D", "E", "F", "A", "B"
If cell value is "C" - Sort order needs to be "D", "E", "F", "A", "B", "A"
Etc etc.

I've tried adding If's to.....

Application.AddCustomList _
ListArray:=Array("B", "C", "D", "E", "F", "A")

 

[Fluff]

How about

   Dim Srt As Variant
   
   Select Case Range("V3")
      Case "A": Srt = Array("B", "C", "D", "E", "F", "A")
      Case "B": Srt = Array("C", "D", "E", "F", "A", "B")
   End Select
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Range("X2"), xlSortOnValues, xlAscending, Join(Srt, ","), xlSortNormal
      .SetRange Range("v2:ae52")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With

There is no need to add anything to the customsort list

 

[dlee83]

Super!
Thank you

 

[Fluff]

You're welcome & thanks for the feedback.