Sorting with variable key VBA

[EduG]

I want the user of my spreadsheet to tell me which columns should be used to sort the data. e.g. column A and F. So I defined variables and try to use the variabel in my sort function. But it is not recognizing the variables in my sort function.
Here is my code (cell A2 = A, cell B2 = F).


Dim Col1 As Range
Set Col1 = Worksheets("Settings").Range("A2")
Dim Col2 As Range
Set Col2 = Worksheets("Settings").Range("B2")

Sheets("test1").Select
Range("A2:AX1000").Select
ActiveWorkbook.Worksheets("test1").Sort.SortFields.Add Key:=Range(Col1) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("test1").Sort.SortFields.Add Key:=Range(Col2) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("test1").Sort
.SetRange Range("A2:AX1000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

 

[Andrew Poulsom]

Welcome to the Board.

You already have Range objects so instead of eg:

Range(Col1)

you just need:

Col1

 

[xld]

Try this

Dim Col1 As Variant
Col1 = Worksheets("Settings").Range("A2").Value2
Dim Col2 As Variant
Col2 = Worksheets("Settings").Range("B2").Value2

With ActiveWorkbook.Worksheets("test1")

    .Sort.SortFields.Add _
        Key:=.Range(Col1), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    .Sort.SortFields.Add _
        Key:=.Range(Col2), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    With .Sort
        .SetRange .Range("A2:AX1000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

 

[EduG]

I tried both options. Andrews options sorts the wrong columns, xld solution gives an error.

If I replace the col1 by "A2" and col2 by "F2" in my programming than it works fine. So the trick must be in creating the variables col1 and col2.

 

[Andrew Poulsom]

Maybe you need eg:

Key:=Range(Col1.Value & 2)

 

[EduG]

Thanks for the help, that plus some other minor changes did the trick. For future reference, here is the code I used.

Dim Col1 As Range
Set Col1 = Worksheets("Settings").Range("A2")
Dim Col2 As Range
Set Col2 = Worksheets("Settings").Range("B2")

Sheets("test1").Select
Range("A2:AX1000").Select
ActiveWorkbook.Worksheets("test1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("test1").Sort.SortFields.Add Key:=Range(Col1.Value & 2) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("test1").Sort.SortFields.Add Key:=Range(Col2.Value & 2) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("test1").Sort
.SetRange Range("A2:AX1000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub