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")
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")