TheRedCardinal
Active Member
- Joined
- Jul 11, 2019
- Messages
- 250
- Office Version
- 365
- 2021
- Platform
- Windows
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:
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?
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:
VBA 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?