building a sort string

I have set up a userform for a context sensitive sort routine which selects the relevant sort keys (up to 3) and sets ascending/descending order. However. from this I need to be able to "build" the sort. I know the basic syntax eg

    Range("AI8:AM12").Select
    Selection.Sort Key1:=Range("AI9"), Order1:=xlAscending, Key2:=Range("AJ9" _
        ), Order2:=xlAscending, Key3:=Range("AL9"), Order3:=xlAscending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
        , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal

I can vary the ranges to what I want (using Range (cells(x,y),cells(w,z))notation) but I need to be able to set the order for each sort key to either ascending or descending, and I need to be able to set up up to 3 sort keys. I can build up a suitable string to define the sort that I want but don't know how to "attach" the result to the sort statement. eg if I build

sort$= 
"key1:=Range (cells(5,12),cells(23,17)),Order1="&order$&"Header:=.

...etcetc"


I can't simply type selection.sort sort$. How can I do it

Any help greatly appreciated

Good morning Andrew Hicks

Try this little experiment - it should help demonstrate what you are trying to achieve. In the range A1:A10 put the numbers 1 to 10 in any order, then run this macro :

Sub test()
a = xlAscending
Range("A1:A10").Sort Key1:=Range("A1"), Order1:=a
End Sub

Then change the value of "a" to xlDescending and run it again.

By the way, please try and "wrap" your code next time you post - makes it much easier for others to read. Thanks.

HTH

DominicB

Hi Andrew Hicks

While xlDescending is a text string (to us humans), as with all xl (and vb) constants, it does have a numeric equivalent, which XL translates at run-time.

To use the numeric eqivalents use 1 for ascending and 2 for descending.

HTH

DominicB