Using formulas for sort fields in Excel VBA

[Bunglesmate]

Hello the team,

I am trying to use formulas in my vba code to generate sort fields.

I have a sheet which will have a column with a title "Use this to sort". The column might not be in the same place each time the macro is run.

I can use code to determine which column this is in but then how do I create a sort field using this variable?

[COLOR=#0000ff]'Determine the column which has "Use this to sort" as a header and set the column number[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]    Cells.Find(What:="Use this to sort", After:=ActiveCell, LookIn:= _[/COLOR]
[COLOR=#0000ff]        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _[/COLOR]
[COLOR=#0000ff]        , MatchCase:=False, SearchFormat:=False).Activate[/COLOR]
[COLOR=#0000ff]    [/COLOR]
[INDENT][COLOR=#0000ff]UseThisToSortCol = ActiveCell.Column[/COLOR][/INDENT]

The sort code generated from the macro recorder is as follows as I selected columns D & K to sort on manually.

[COLOR=#0000ff]ActiveSheet.Sort.SortFields.Clear[/COLOR]
[COLOR=#0000ff]    ActiveSheet.Sort.SortFields.Add Key:=Range( _[/COLOR]
[COLOR=#0000ff]    "D2:D10147"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _[/COLOR]
[COLOR=#0000ff]        xlSortNormal[/COLOR]
[COLOR=#0000ff]    ActiveSheet.Sort.SortFields.Add Key:=Range( _[/COLOR]
[COLOR=#0000ff]        "K2:K10147"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _[/COLOR]
[COLOR=#0000ff]        xlSortNormal[/COLOR]
[COLOR=#0000ff]    With ActiveSheet.Sort[/COLOR]
[COLOR=#0000ff]        .SetRange Range("A1:R10147")[/COLOR]
[COLOR=#0000ff]        .Header = xlYes[/COLOR]
[COLOR=#0000ff]        .MatchCase = False[/COLOR]
[COLOR=#0000ff]        .Orientation = xlTopToBottom[/COLOR]
[COLOR=#0000ff]        .SortMethod = xlPinYin[/COLOR]
[COLOR=#0000ff]        .Apply[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]

What I'd like to do is use the variable 'UseThisToSortCol', and others to determine the sort column.

If I try to substitute the following

[COLOR=#0000FF]ActiveSheet.Sort.SortFields.Clear[/COLOR]
[COLOR=#0000FF]    ActiveSheet.Sort.SortFields.Add Key:=[/COLOR][COLOR=#ee82ee]Range( _[/COLOR]
[COLOR=#ee82ee]    "D2:D10147")[/COLOR][COLOR=#0000FF], SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _[/COLOR]
[COLOR=#0000FF]        xlSortNormal[/COLOR]

with

[COLOR=#0000ff]ActiveSheet.Sort.SortFields.Clear[/COLOR]
[COLOR=#0000ff]    ActiveSheet.Sort.SortFields.Add Key:=[/COLOR][COLOR=#ee82ee]cells( _[/COLOR]
[COLOR=#ee82ee]        2, ComponentUnitCol:10147,ComponentUnitCol )[/COLOR][COLOR=#0000ff], SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _[/COLOR]
[COLOR=#0000ff]        xlSortNormal[/COLOR]

I get a compilation error.

Is this even possible?

All searches online so far, including MrExcel, do not seem to return the answers I am after. Maybe I am asking the wrong question.

Any help gratefully received.

Share & Enjoy.

Zippy.

¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬
Using MS Office Professional Plus 2010
Excel version 14.0.6123.5001 (32bit)

 

[Bunglesmate]

Hello the team,

I have finally worked it out. Me muppet .

Thought I'd post it anyway in case anyone else was needing the answer.

ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
    [COLOR=#008000][B]"D2:D10147"[/B][/COLOR]), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

Has been changed for the following

    ActiveSheet.Sort.SortFields.Add Key:=Range( _
    [B][COLOR=#008000]Cells(2, ComponentUnitCol), Cells(activeDatarowintab + 1, ComponentUnitCol)[/COLOR][/B]), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

where ComponentUnitCol & activeDatarowintab are variables computed earlier in the macro.

Need to remember that the first one "D2:D10147" is in columnrow format whereas the second one is in row,column format.

Share & Enjoy.

Zippy.