VBA type mismatch on CustomOrder

Question

I have this code that works great:

Sub NewSortTest()
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add key:=Range("A1:A20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "alpha,bravo,charlie,delta,echo,foxtrot,golf,hotel,india,juliet", DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I am trying to modify the above code with a variable keyRangefor CustomOrder:=:

Sub NewSortTest()
    Dim keyRange As String

    keyRange = "alpha,bravo,charlie,delta,echo,foxtrot,golf,hotel,india,juliet"

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add key:=Range("A1:A20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        keyRange, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

However, I get a type mismatch error. On MSDN, I see that it CustomOrder:=has a type Variant. I tried Variantinstead String, but I get the same error.

Edit: CustomOrder actually has a SortField type . How can I convert a String keyRange to a SortField object?

Answer 1

Glad you understood that. The following also works (for this post ):

Sub NewSortTest()
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A20"), _
                                                        SortOn:=xlSortOnValues, _
                                                        Order:=xlAscending, _
                                                        CustomOrder:=keyRange, _
                                                        DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:B20")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

Function keyRange() As String

keyRange = "alpha,bravo,charlie,delta,echo,foxtrot,golf,hotel,india,juliet"

End Function

Edit: even easier

CustomOrder:=CVar(keyRange)

Edit: why does this work?

. CustomOrder , . , , , . , CustomOrder . , String Long. poblem String Variant. String. - String, . , , , " " String. -, , .

Answer 2

. ListArray:

Sub NewSortTest()
    Dim keyRange As Variant
    Dim sortNum As Long

    keyRange = Array("alpha", "bravo", "charlie", "delta", "echo", "foxtrot", "golf", "hotel", "india", "juliet")

    Application.AddCustomList ListArray:=keyRange
    sortNum = Application.CustomListCount

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add key:=Range("A1:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=sortNum, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Answer 3

, , , .

SortField , CustomOrder, , , , . loNewReport ListObject

Dim sField As SortField
Set sField = loNewReport.Sort.SortFields.Add(loNewReport.ListColumns("Branch").DataBodyRange, xlSortOnValues, xlAscending)
sField.CustomOrder = "QLD, NSW, VIC"
loNewReport.Sort.Apply
Set sField = Nothing

Answer 4

, .

, , , , , , .

In any case, it works like this, thanks to previous respondents. This is what I came up with to solve my problem. keyRangefits into a string .SortFields.Addas shown in previous answers.

Function keyRange() As String
    'Create a string used to sort data on another sheet.
    'Becomes part of the .SortFields.Add statement as CustomOrder:=keyfield
    'CustomOrder items are in Col "A" of a sheet. The list can change over time
    'and is refreshed from an import from a database.
    With Sheets("Home Groups")
        lic = lastInCol(.Range("A1"))       'Gets the last used cell in column A
                                            'lastInCol is one of John Walkenbach routines
                                            'From his book Excel 2010 Power Programming with VBA
       'Create a string from those items from row 2 to the last used row
        For rw = 2 To lic
            If rw = 2 Then
                keyRange = .Cells(rw, 1)        'This is the first item
            Else
                keyRange = keyRange & "," & .Cells(rw, 1)   'The remaining items - comma separated
            End If
        Next rw
    End With
End Function