Can I sort a table by applying columns in a different order to the sort request?

[TheRedCardinal]

I have a table on a worksheet with upto 15 column headers.

Elsewhere in my workbook, I have a "Settings" sheet which contains these 15 headers and various settings associated with those columns, such as format, width, bold or not etc.
One of them is "Sort" and the column for that consists of either a blank (don't sort) or a number (the order of the sort)

My macro loads these into an array in order of sorting preference, so that Array(1) contains the column number of the first priority, Array(2) the second etc.

But when I try to apply those codes I get a Type Mismatch error. I think it's because I'm referencing the column wrong.

Here is my code:

With WS2.ListObjects(1).Sort

    .SortFields.Clear
    
    If SortColNumbers(1) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(1)), SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(2) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(2)), SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(3) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(3)), SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(4) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(4)), SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(5) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(5)), SortOn:=xlSortOnValues, Order:=xlAscending
    
    .Header = xlYes
    .SortMethod = xlPinYin
    .Apply
    
End With

During testing, my array had number 3, 8 and 6, and the other two are empty.

It fails on the first line, but after "passing" the IF test.

What have I done wrong?

 

[Vogateer]

Can you check the type of your SortColNumbers(1) variable?

 

[TheRedCardinal]

Can you check the type of your SortColNumbers(1) variable?

It was initialised as a Variant.

Should it be more specific?

 

[Vogateer]

In this case I think it would be better to initialize your array it as an array of integers or maybe long numbers. I'm not 100% on this, but I think .ListColumns() is expecting an integer or maybe a long number and it's getting something else that it can't handle.

 

[TheRedCardinal]

Hi,
Sorry for the delay - I took a break from Excel for the weekend!

I have set the array up as both Long and Integer and I get the same error in the same place.

Thanks for the idea...

 

[TheRedCardinal]

I have sorted it.

I needed to specifically refer to the range, not just the column:

With WS2.ListObjects(1).Sort

    .SortFields.Clear
    
    If SortColNumbers(1) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(1)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(2) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(2)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(3) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(3)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(4) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(4)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(5) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(5)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    
    .Header = xlYes
    .SortMethod = xlPinYin
    .Apply
    
End With

Works now!