I've managed to put together a custom sort macro, and it works fine... to a degree. I want to take it to the next degree and am at loss on how to proceed. In short, the custom sort is ascending levels of distribution according to a two-column "mapping" system, and a starting-point reference. My macro sorts the proper levels, but within each level I want the items sorted ascending alphanumerically (i.e. standard ascending sort). My current macro just arranges by level, and top down within each level, with no regard to alphanumeric value.
Any ideas how to accomplish this?
Sub CustomSort()
'Create custom sort list & sort "Panels" by "Fed By" column
Dim cell As Range
Dim cell2 As Range
Dim val As String
Dim x As Long
Dim i As Integer
Dim rng As Range
Sheets("Project Summary").Select
'Refresh sort list to cover for potential changes
x = Range("panel_sort").Rows.Count
If x > 1 Then
Range("panel_sort").Offset(1, 0).Resize(x - 1, 1).ClearContents
End If
i = 0
Do While Range("panel_sort").Rows.Count < Range("Panels[Fed By]").Rows.Count + 1
i = i + 1
val = Range("panel_sort").Offset(i - 1, 0).Resize(1, 1).Value
For Each cell2 In Range("Panels[Fed By]")
x = Range("panel_sort").Rows.Count
If cell2.Value = val Then
Range("panel_sort").Offset(x, 0).Resize(1, 1).Value = cell2.Offset(0, -2).Value
End If
Next cell2
Loop
'Create custom sort list
Set rng = Range("panel_sort")
Application.AddCustomList (rng)
'Sort "Panels" with custom sort list
ActiveWorkbook.Worksheets("Project Summary").Range("Panels").Sort Key1:=Range("Panels[Fed By]"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=Application.CustomListCount + 1, MatchCase:=False
'Delete custom sort list
Application.DeleteCustomList Application.CustomListCount
End Sub
Bookmarks