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

 

[zookeepertx]

Thank you, MoshiM, for your help and putting up with my stupid questions, but I give up. This sorting was supposed to be the easy part and was to set up the spreadsheet for the main part of the macro, but I can't even get this figured out! I had this crazy idea that maybe I could create a macro on my own, instead of having to get someone else here to write most, if not all, of it. Apparently I can't. Since my co-worker needs this ASAP (like, yesterday would be good!), I think I'm just going to start a new topic showing the entire problem and hope someone here can dive in. Otherwise, it'll take me days or weeks to get anything done.

Thanks again for your input and coding; you tried, but I'm just dense.

Jenny

 

[zookeepertx]

Thank you, shg, for your help and putting up with my stupid questions, but I give up. This sorting was supposed to be the easy part and was to set up the spreadsheet for the main part of the macro, but I've been on just THAT for 2 afternoons and failed! I had this crazy idea that maybe I could create a macro on my own, instead of having to get someone else here to write most, if not all, of it. Apparently I can't. Since my co-worker needs this ASAP (like, yesterday would be good!), I think I'm just going to start a new topic showing the entire problem and hope someone here can dive in. Otherwise, it'll take me days or weeks to get anything done.

Thanks again for your input and coding; you tried, but I'm just dense.

Jenny

 

[MoshiM]

Thank you, MoshiM, for your help and putting up with my stupid questions, but I give up. This sorting was supposed to be the easy part and was to set up the spreadsheet for the main part of the macro, but I can't even get this figured out! I had this crazy idea that maybe I could create a macro on my own, instead of having to get someone else here to write most, if not all, of it. Apparently I can't. Since my co-worker needs this ASAP (like, yesterday would be good!), I think I'm just going to start a new topic showing the entire problem and hope someone here can dive in. Otherwise, it'll take me days or weeks to get anything done.

Thanks again for your input and coding; you tried, but I'm just dense.

Jenny

Did what i propose to fix column D not work?

 

[zookeepertx]

Did what i propose to fix column D not work?

I tried to work with it but I was so flustered and mad at myself by then, that I just made things worse, somehow. I don't even know how, but I was giving myself a nervous twitch by then; that didn't help. Once I start freaking out, there's not much coming back from it. Plus, I realized that I need to get going on the "meat" of the macro and was spending too much time on the set-up to make it "look nice". (I do that a LOT!)

Anyway, I just made another post describing the entire macro which, now that I look at it, looks like total gibberish, so I have a feeling nobody's going to want to mess with it. <sigh>

Jenny

 

[MoshiM]

I tried to work with it but I was so flustered and mad at myself by then, that I just made things worse, somehow. I don't even know how, but I was giving myself a nervous twitch by then; that didn't help. Once I start freaking out, there's not much coming back from it. Plus, I realized that I need to get going on the "meat" of the macro and was spending too much time on the set-up to make it "look nice". (I do that a LOT!)

Anyway, I just made another post describing the entire macro which, now that I look at it, looks like total gibberish, so I have a feeling nobody's going to want to mess with it. <sigh>

Jenny

I looked over the original list and saw that some of the items in the array you were created above the sort block and the one you used for column D had the same elements but a different number of spaces in the string so double check the contents of the list at the top of the now semicolon delimited array. If there are no leading or trailing spaces intended for the field then do nothing.

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

Dim Name_K() As String, WK As Worksheet,  My_C As Variant, X As Long
Set My_C = CreateObject("Scripting.Dictionary")


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


My_C.Add "1", Name_K


For Each Item In My_C.Items 'loops both arrays and removes spaces from the start and end


    For X = LBound(Item) To UBound(Item)
        
        Item(X) = Trim(Item(X))


    Next X
    
Next Item


a = My_C.Items
'a (1)
'For X = 0 To 14
'
'MsgBox Name_K(X) & "----" & Name_D_Exception(X)
'
'Next X


Set WK = ActiveWorkbook.Worksheets("Audit-raw data-trimmed (2)")
        
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:=a(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

</sigh>

 

[zookeepertx]

I'm not sure where the discrepancy in the number of spaces that you're seeing is and I've been looking at it so long that I'm half blind, so I don't expect I'll spot it. In fact, I only see the list once - up where the array is being created. One thing I DO see is that, in the code, "Invoice,Commercial" is all run together but in the document, there's a space between before Commercial, making it "Invoice, Commercial". Is that a factor?

Anyway, here's what I had yesterday when I left (after messing with it and probably screwing it up):

Sub SortTest()
    Dim WK As Worksheet
    Set WK = ActiveWorkbook.Worksheets("Audit-raw data-trimmed (2)")

    Application.AddCustomList ListArray:=Array("Broker Reference Number", _
        "Division", "Entry Number", "Imports - Document Type", "Name")
    
    WK.Sort.SortFields.Clear
    
    WK.Sort.SortFields.Add2 _
        Key:=Range("A1:O1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        CustomOrder:= _
        "Broker Reference Number,Division,Entry Number,Imports - Document Type,Name", _
        DataOption:=xlSortNormal
    
    With WK.Sort
        .SetRange Range("A1:O40")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    
Dim Name_K() As String
Dim Name_D_Exception() As String
Name_D_Exception = 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 = 4 To UBound(Name_D_Exception) - 1 'Adjust the value of the other elements to account for this change

     Name_D_Exception(x) = Name_D_Exception(x + 1)

Next x

ReDim Preserve Name_D_Exception(LBound(Name_D_Exception) To UBound(Name_D_Exception) - 1)
Application.AddCustomList ListArray:=Name_D_Exception
    
WK.Sort.SortFields.Clear

For x = 65 To 69

    Select Case x
    
        Case 68
[COLOR=#ff0000]            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(x) & 2 & ":" & Chr(x) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=Name_D_Exception, DataOption:=xlSortNormal[/COLOR]
        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

It fails with a Type Mismatch error at the block that I've colored red.

Here's a link to the newer post I made that includes depictions of what the data looks like and what I need to MAKE it look. https://www.mrexcel.com/forum/excel...sing-out-data-move-results-another-sheet.html

I won't be back to work until Monday, so I probably won't be back here to post until then.

Jenny

 

[MoshiM]

I'm not sure where the discrepancy in the number of spaces that you're seeing is and I've been looking at it so long that I'm half blind, so I don't expect I'll spot it. In fact, I only see the list once - up where the array is being created. One thing I DO see is that, in the code, "Invoice,Commercial" is all run together but in the document, there's a space between before Commercial, making it "Invoice, Commercial". Is that a factor?

Anyway, here's what I had yesterday when I left (after messing with it and probably screwing it up):



It fails with a Type Mismatch error at the block that I've colored red.

Here's a link to the newer post I made that includes depictions of what the data looks like and what I need to MAKE it look. https://www.mrexcel.com/forum/excel...sing-out-data-move-results-another-sheet.html

I won't be back to work until Monday, so I probably won't be back here to post until then.

Jenny

Have you tried using the most recent code I supplied instead of one of the older ones?

 

[zookeepertx]

Have you tried using the most recent code I supplied instead of one of the older ones?

Hi there

I was pretty sure I'd tried your latest code and it gave an error, but I tried it again just now and the same thing happens: I get a type mismatch error at the line highlighted red below. I thought maybe the extra spaces around the semi-colons were being a problem so I removed them and tried again. It failed at the same spot, and said "Subscript out of range", but only when it gets to where it's looking at column D.

Sub Sort()
Dim Name_K() As String, WK As Worksheet, My_C As Variant, X As Long
Set My_C = CreateObject("Scripting.Dictionary")

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", ";")
My_C.Add "1", Name_K

For Each Item In My_C.Items 'loops both arrays and removes spaces from the start and end

    For X = LBound(Item) To UBound(Item)
        Item(X) = Trim(Item(X))
    Next X
    
Next Item

a = My_C.Items
'a (1)
'For X = 0 To 14
'MsgBox Name_K(X) & "----" & Name_D_Exception(X)
'Next X

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

For X = 65 To 69
    Select Case X
    
        Case 68
[COLOR=#ff0000]            WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=a(1), DataOption:=xlSortNormal[/COLOR]
        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 don't know why it doesn't like me!

I'll be here till 3:39 CST and then out until Monday, so I'll be back then. (I'd play with this at home, but I have a "basic" version of Excel there and can't do macros from that computer)

Have a good weekend!

Jenny

 

[MoshiM]

Hi there

I was pretty sure I'd tried your latest code and it gave an error, but I tried it again just now and the same thing happens: I get a type mismatch error at the line highlighted red below. I thought maybe the extra spaces around the semi-colons were being a problem so I removed them and tried again. It failed at the same spot, and said "Subscript out of range", but only when it gets to where it's looking at column D.

I just don't know why it doesn't like me!

I'll be here till 3:39 CST and then out until Monday, so I'll be back then. (I'd play with this at home, but I have a "basic" version of Excel there and can't do macros from that computer)

Have a good weekend!

Jenny

I delimited the string with a semicolon. Any spaces not in-between two letters are removed automatically. Check the string for proper spacing atthe top so it matches with your columns.

Sub Custom_Sort()
Dim Name_K() As String, 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


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


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

 

[zookeepertx]

I delimited the string with a semicolon. Any spaces not in-between two letters are removed automatically. Check the string for proper spacing at the top so it matches with your columns.

Sub Custom_Sort()
Dim Name_K() As String, 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)
        
        [COLOR=#0000ff]Name_K(X) = Application.WorksheetFunction.Trim(Name_K(X))[/COLOR]
    
    Next X

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

WK.Sort.SortFields.Clear

For X = 65 To 69

    Select Case X
    
        Case 68
            [COLOR=#ff0000]WK.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=Name_K, DataOption:=xlSortNormal[/COLOR]
        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

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