Custom Sort - problem with a comma in an entry

[zookeepertx]

Hello all!

I'm trying to set up a custom sort in VBA, but I ran into a problem. I'm establishing the array to use for the sort and it's fine except that one of the items being sorted is called "Invoice, Commercial" and it's affecting the sort, since there are commas separating the array elements. Here's the full custom list:

"Broker's Invoice,Bill of Lading,Cargo Release,Invoice,Commercial,Packing List,CF 7501,Rated Invoice,Delivery Order,Receiving,Payment (Debit) Advice,FWS3177,CITES,NMG Audit,Checklist,CF 7501-REVISED", _
DataOption:=xlSortNormal

Also, VBA doesn't seem to want to let me wrap that long list to encompass 2-3 lines in the code. Does it just HAVE to string out way to the right or is there some way other than space/underscore/enter?

Thank you!

Jenny

 

[MoshiM]

Hello again!
I started a whole new module and copied and pasted in the code in your most recent reply, but I'm still getting a "Run-time error '13': Type mismatch" error on the section I've highlighted red above. If I hover over the line I've highlighted blue above, it says "Name_K (X) = <subscript out="" of="" range="">". I assume that might be related, right?

Jenny

Let's try using a variant array instead of a string array then

Sub Custom_Sort()
Dim Name_K As Variant, WK As Worksheet, X As Long, sortNum As Long




Name_K = Split("Broker's Invoice; Bill of Lading; Cargo Release; Invoice,Commercial; Packing List; CF 7501; Rated Invoice; Delivery Order; Receiving; Payment (Debit) Advice; FWS3177; CITES; NMG Audit; Checklist; CF 7501-REVISED", ";")


    For X = LBound(Name_K) To UBound(Name_K)
        
        Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X))
    
    Next X


Set WK = ActiveWorkbook.Worksheets("Audit-raw data-trimmed (6)")


'    Application.AddCustomList ListArray:=Name_K
'    sortNum = Application.CustomListCount


WK.Sort.SortFields.Clear


For X = 65 To 69


    Select Case X
    
        Case 68
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=Name_K, DataOption:=xlSortNormal
        Case Else
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
    
    End Select
    
Next X
       
    With WK.Sort
        .SetRange Range("A1:O39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

</subscript>

 

[zookeepertx]

Let's try using a variant array instead of a string array then

Sub Custom_Sort()
Dim Name_K As Variant, WK As Worksheet, X As Long, sortNum As Long

Name_K = Split("Broker's Invoice; Bill of Lading; Cargo Release; Invoice,Commercial; Packing List; CF 7501; Rated Invoice; Delivery Order; Receiving; Payment (Debit) Advice; FWS3177; CITES; NMG Audit; Checklist; CF 7501-REVISED", ";")

    For X = LBound(Name_K) To UBound(Name_K)
        
        Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X))
    
    Next X

Set WK = ActiveWorkbook.Worksheets("Audit-raw data-trimmed (6)")

'    Application.AddCustomList ListArray:=Name_K
'    sortNum = Application.CustomListCount

WK.Sort.SortFields.Clear

For X = 65 To 69

    Select Case X
    
        Case 68
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=Name_K, DataOption:=xlSortNormal
        Case Else
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
    
    End Select
    
Next X
       
    With WK.Sort
        .SetRange Range("A1:O39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

I just tried that and ended up in the same spot I was with the other code as far as errors:

• "Type Mismatch" on the first section of "WK.Sort.SortFields.Add2......" but not until it's looking at column D (Case 68). ONCE THAT HAS HAPPENED THE FOLLOWING MESSAGES OCCUR.
• On the line "Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X)), when the macro fails, if I hover over the Name_K part, it says "Subscript out of range"; if I hover over the (X) part, it correctly says "68". (I don't know what happened to the rest of my sentence on this in my previous post)
• And, I didn't notice this before, but when I hover over "WK" sometimes the word "Nothing" pops up but sometimes there isn't a pop-up.

Are custom sorts usually this big of a pain in a macro? It didn't SEEM like it should be. But then, I'm wrong a lot, too, LOL!

Jenny

 

[MoshiM]

I just tried that and ended up in the same spot I was with the other code as far as errors:

• "Type Mismatch" on the first section of "WK.Sort.SortFields.Add2......" but not until it's looking at column D (Case 68). ONCE THAT HAS HAPPENED THE FOLLOWING MESSAGES OCCUR.
• On the line "Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X)), when the macro fails, if I hover over the Name_K part, it says "Subscript out of range"; if I hover over the (X) part, it correctly says "68". (I don't know what happened to the rest of my sentence on this in my previous post)
• And, I didn't notice this before, but when I hover over "WK" sometimes the word "Nothing" pops up but sometimes there isn't a pop-up.

Are custom sorts usually this big of a pain in a macro? It didn't SEEM like it should be. But then, I'm wrong a lot, too, LOL!

Jenny

I assume you are getting the subscript out of range error for the trim function after you have reached the Case 68 line. In which case it is because I reused the same variable since that section of the code is no longer needed. You can't find the 68th object of an array that only has 10 objects. As for your other concern, is it before or after the code has reached the "Set WK=" line ? Verify that that your worksheet names have not changed and are accurate if it is after.

Sub Custom_Sort()
Dim Name_K As Variant, WK As Worksheet, X As Long, sortNum As Long


Name_K = Split("Broker's Invoice; Bill of Lading; Cargo Release; Invoice,Commercial; Packing List; CF 7501; Rated Invoice; Delivery Order; Receiving; Payment (Debit) Advice; FWS3177; CITES; NMG Audit; Checklist; CF 7501-REVISED", ";")


    For X = LBound(Name_K) To UBound(Name_K)
        
        Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X))
    
    Next X


Set WK = thisWorkbook.Worksheets("Audit-raw data-trimmed (6)")


    Application.AddCustomList ListArray:=Name_K




WK.Sort.SortFields.Clear


For X = 65 To 69


    Select Case X
    
        Case 68
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=Application.CustomListCount + 1, DataOption:=xlSortNormal
        Case Else
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
    
    End Select
    
Next X
       
    With WK.Sort
        .SetRange Range("A1:O39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

 

[zookeepertx]

I assume you are getting the subscript out of range error for the trim function after you have reached the Case 68 line. In which case it is because I reused the same variable since that section of the code is no longer needed. You can't find the 68th object of an array that only has 10 objects. As for your other concern, is it before or after the code has reached the "Set WK=" line ? Verify that that your worksheet names have not changed and are accurate if it is after.

Sub Custom_Sort()
Dim Name_K As Variant, WK As Worksheet, X As Long, sortNum As Long

Name_K = Split("Broker's Invoice; Bill of Lading; Cargo Release; Invoice,Commercial; Packing List; CF 7501; Rated Invoice; Delivery Order; Receiving; Payment (Debit) Advice; FWS3177; CITES; NMG Audit; Checklist; CF 7501-REVISED", ";")

    For X = LBound(Name_K) To UBound(Name_K)
        
        Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X))
    
    Next X

Set WK = thisWorkbook.Worksheets("Audit-raw data-trimmed (6)")

    Application.AddCustomList ListArray:=Name_K

WK.Sort.SortFields.Clear

For X = 65 To 69

    Select Case X
    
        Case 68
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=Application.CustomListCount + 1, DataOption:=xlSortNormal
        Case Else
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
    
    End Select
    
Next X
       
    With WK.Sort
        .SetRange Range("A1:O39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

When I step through the macro and its running through the Trim Function, if I hover over "Name_(K)" on each loop, it shows the word/words associated with that loop. (For example: Broker's Invoice or Bill of Lading). But when the last loop is done and the cursor moves to the "Set WK" line, suddenly if I hover over "Name_(K)", then it says Subscript out of Range. However, hovering over the "X" makes it say "15", but how can that be when the For X loop only tells it to go to UBound, which is 14??

Anyway, while the Set WK line is highlighted, hovering over WK says "nothing" but when I move to the line Application.AddCustomList, nothing happens at all when I hover over WK. The worksheet name is the same, although I do have to alter that in the code before running it, depending on whether I've made a new copy of the sheet to work with. Can I just use ActiveWorkbook.ActiveWorksheet or something to avoid that?

So, when I step to the line Select Case X then X has changed to 65 when I hover over it. For Case 65, 66 and 67, it skips down to the Case Else section like it should but when it gets to where the Case is 68, that's when it fails with the Run-time error 13: Type mismatch message.

It just occurred to me to wonder if we HAVE to use the Case 68 (which I gather specifies D) since we know that it's only column D that needs the custom sort. Is there a way to sort A, B, C as normal but D as custom and E as normal by just using the column letters? (Probably not, or you would have thought of that already, but I was just curious if not, then WHY not)

 

[MoshiM]

When I step through the macro and its running through the Trim Function, if I hover over "Name_(K)" on each loop, it shows the word/words associated with that loop. (For example: Broker's Invoice or Bill of Lading). But when the last loop is done and the cursor moves to the "Set WK" line, suddenly if I hover over "Name_(K)", then it says Subscript out of Range. However, hovering over the "X" makes it say "15", but how can that be when the For X loop only tells it to go to UBound, which is 14??

Anyway, while the Set WK line is highlighted, hovering over WK says "nothing" but when I move to the line Application.AddCustomList, nothing happens at all when I hover over WK. The worksheet name is the same, although I do have to alter that in the code before running it, depending on whether I've made a new copy of the sheet to work with. Can I just use ActiveWorkbook.ActiveWorksheet or something to avoid that?

So, when I step to the line Select Case X then X has changed to 65 when I hover over it. For Case 65, 66 and 67, it skips down to the Case Else section like it should but when it gets to where the Case is 68, that's when it fails with the Run-time error 13: Type mismatch message.

It just occurred to me to wonder if we HAVE to use the Case 68 (which I gather specifies D) since we know that it's only column D that needs the custom sort. Is there a way to sort A, B, C as normal but D as custom and E as normal by just using the column letters? (Probably not, or you would have thought of that already, but I was just curious if not, then WHY not)

At the end of a for loop the variable will always be one more than intended.
We can use the letters, but one of your original requests was to shorten the code so I used the CHR() function along with an ASCII table to loop through the needed letters (hence the loop for 65 to 69 with an exception for case 68).
Try this one out:

Sub Custom_Sort()
Dim Name_K As Variant, WK As Worksheet, X As Long, sortNum As Long


Name_K = Split("Broker's Invoice; Bill of Lading; Cargo Release; Invoice,Commercial; Packing List; CF 7501; Rated Invoice; Delivery Order; Receiving; Payment (Debit) Advice; FWS3177; CITES; NMG Audit; Checklist; CF 7501-REVISED", ";")


    For X = LBound(Name_K) To UBound(Name_K)
        
        Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X))
    
    Next X


    Application.AddCustomList ListArray:=Name_K
    
    sortNum = Application.CustomListCount
    
    
Set WK = ThisWorkbook.activesheet


WK.Sort.SortFields.Clear


For X = 65 To 69 'A TO E


    Select Case X
    
        Case 68 'D
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=sortNum, DataOption:=xlSortNormal
        Case Else 'Not D
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
    
    End Select
    
Next X
       
    With WK.Sort
        .SetRange Range("A1:O39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

Also look over these:
https://stackoverflow.com/questions/41303940/custom-sort-on-range-vba
https://www.excelforum.com/excel-pr...ortfields-add-customorder-use-a-variable.html
https://stackoverflow.com/questions/36576540/sorting-using-a-custom-order-in-excel-gives-error-1004
https://www.ozgrid.com/forum/forum/...e-mismatch-sorting-with-custom-order-list-vba
https://stackoverflow.com/questions/22366188/vba-type-mismatch-on-customorder?noredirect=1
https://stackoverflow.com/questions...-a-custom-order-and-a-value-containing-commas

 

[zookeepertx]

At the end of a for loop the variable will always be one more than intended.
We can use the letters, but one of your original requests was to shorten the code so I used the CHR() function along with an ASCII table to loop through the needed letters (hence the loop for 65 to 69 with an exception for case 68).
Try this one out:

Sub Custom_Sort()
Dim Name_K As Variant, WK As Worksheet, X As Long, sortNum As Long

Name_K = Split("Broker's Invoice; Bill of Lading; Cargo Release; Invoice,Commercial; Packing List; CF 7501; Rated Invoice; Delivery Order; Receiving; Payment (Debit) Advice; FWS3177; CITES; NMG Audit; Checklist; CF 7501-REVISED", ";")

    For X = LBound(Name_K) To UBound(Name_K)
        
        Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X))
    
    Next X

    Application.AddCustomList ListArray:=Name_K
    
    sortNum = Application.CustomListCount
      
Set WK = ThisWorkbook.activesheet

WK.Sort.SortFields.Clear

For X = 65 To 69 'A TO E

    Select Case X
    
        Case 68 'D
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=sortNum, DataOption:=xlSortNormal
        Case Else 'Not D
            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
    
    End Select
    
Next X
       
    With WK.Sort
        .SetRange Range("A1:O39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Hey! That's progress! It doesn't fail, it just doesn't sort at all, LOL! None of the columns do. And stepping through the code, the "Subscript out of Range" message is still there for Name_K. But, I consider it progress that it didn't fail and seize up!

Jenny

 

[MoshiM]

Hey! That's progress! It doesn't fail, it just doesn't sort at all, LOL! None of the columns do. And stepping through the code, the "Subscript out of Range" message is still there for Name_K. But, I consider it progress that it didn't fail and seize up!

Jenny

Now try changing sortNum to variant at the top.

 

[zookeepertx]

Now try changing sortNum to variant at the top.

I assume you mean on the Dim line, right?
I just did that and it works just like yesterday: it doesn't actually FAIL, just doesn't do anything. Stepping through, hovering over Name_K still shows "Subscript out of range". But this time, hovering over sortNum in the Case 68 section shows the number 14.

Jenny

 

[MoshiM]

I assume you mean on the Dim line, right?
I just did that and it works just like yesterday: it doesn't actually FAIL, just doesn't do anything. Stepping through, hovering over Name_K still shows "Subscript out of range". But this time, hovering over sortNum in the Case 68 section shows the number 14.

Jenny

Name_K is fine as I explained before.
If this one doesn't work then I'm stumped. Have you looked over the links I shared?

Sub Custom_Sort()
Dim Name_K As Variant, WK As Worksheet, X As Long


Name_K = Split("Broker's Invoice; Bill of Lading; Cargo Release; Invoice,Commercial; Packing List; CF 7501; Rated Invoice; Delivery Order; Receiving; Payment (Debit) Advice; FWS3177; CITES; NMG Audit; Checklist; CF 7501-REVISED", ";")


    For X = LBound(Name_K) To UBound(Name_K)


        Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X))
    
    Next X


Application.AddCustomList ListArray:=Name_K
      
Set WK = ThisWorkbook.ActiveSheet


Range("A1:O39").Select


With WK


    .Sort.SortFields.Clear


    For X = 65 To 69 'A TO E
    
        Select Case X
        
            Case 68 'D
                
                .Sort.SortFields.Add2 Key:=.Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, _
                Order:=xlAscending, CustomOrder:=Application.CustomListCount, DataOption:=xlSortNormal
        
            Case Else 'Not D


                .Sort.SortFields.Add2 _
                Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal
        
        End Select
        
    Next X
    
End With


With WK.Sort


        .SetRange Range("A1:O39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
End With


Application.DeleteCustomList Application.CustomListCount


End Sub

 

[zookeepertx]

Name_K is fine as I explained before.
If this one doesn't work then I'm stumped. Have you looked over the links I shared?

Sub Custom_Sort()
Dim Name_K As Variant, WK As Worksheet, X As Long

Name_K = Split("Broker's Invoice; Bill of Lading; Cargo Release; Invoice,Commercial; Packing List; CF 7501; Rated Invoice; Delivery Order; Receiving; Payment (Debit) Advice; FWS3177; CITES; NMG Audit; Checklist; CF 7501-REVISED", ";")

    For X = LBound(Name_K) To UBound(Name_K)

        Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X))
    
    Next X

Application.AddCustomList ListArray:=Name_K
      
Set WK = ThisWorkbook.ActiveSheet

Range("A1:O39").Select

With WK

    .Sort.SortFields.Clear

    For X = 65 To 69 'A TO E
    
        Select Case X
        
            Case 68 'D
                
                .Sort.SortFields.Add2 Key:=.Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, _
                Order:=xlAscending, CustomOrder:=Application.CustomListCount, DataOption:=xlSortNormal
        
            Case Else 'Not D

                .Sort.SortFields.Add2 _
                Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal
        
        End Select
        
    Next X
    
End With

With WK.Sort

        .SetRange Range("A1:O39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
End With

Application.DeleteCustomList Application.CustomListCount

End Sub

Hi again, Moshi.

Sorry to be so long in replying; I was off for a couple of days.

I tried this most recent code you provided, but it doesn't seem to want to do right either. It just doesn't sort at all.
There are 15 items in the custom list; LBound is 0 and UBound is 14, so I gather that accounts for all 15 items. But when I step through the code and get to the section where Case is 68, if I hover over CustomListCount the little popup box says 13! I don't know if that means anything but it seemed odd to me.

I'm completely at a loss.

I haven't gotten a chance to look at the links you provided because I've been catching up from being gone; I'll look at them later.

Thanks

Jenny