"orientaion" parameter in vba SORT

[Kerry H]

The following statements, as part of a VBA sort routine, work as intended.

With ActiveWorkbook.Worksheets(Sh).Sort
.SetRange Range1
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

I use the routine repeatedly in a single Sub to sort on both rows and columns depending on what sheet I am sorting. I would like to replace "xlTopToBottom" with a variable, so that, where appropriate, I can set the orientation variable to 1 or to "xlLeftToRight". This doesn't seem to work. Can it be done?

Thanks.

[p45cal]

I'm sure you can, but realise it isn't the only aspect of the sort that needs to change, see also the Key range (although if you used a single cell as the Key being the intersect of the column you want to sort on and the row you want to sort on, it should work).

[Kerry H]

Thanks, p45cal,
The routine works ok if I use xlRightToLeft explicitly, so it's just a matter of using a variable.
That, I can't get to work.

[p45cal]

Show us your code as it is and we'll tweak.

[Kerry H]

Just going out. will get back to you.

[Kerry H]

Here's a section of the code. The array is read in properly and the parameters r1, rl, etc. are correct. (At one point I was using the boolean value B to set the value of "orient".) I have tried "orient" as a string (eg "xlTopToBottom") and as an integer (1 or 2), defining "orient" properly in each trial. If I use "xlTopToBottom" or "xlLeftToRight" explicitly in place of "orient", the sort works fine. With "orient" as a number, the execution stops on ".Apply" - "Sort reference is not valid". With orient as a string, it stops at ".orientation" with a "type mismatch" error.

For L = 1 To 5

            Sh = XArray(1, L)       ' sheet to be sorted
            r1 = XArray(2, L)       ' first row in whole matrix (col in PREORDER
            rL = XArray(4, L)       ' last row in active matrix  (col in PREORDER
            c1 = XArray(5, L)       ' first col in whole matrix   (row in PREORDER)
            cL = XArray(6, L)       ' last col in whole matrix   (row in PREORDER)
            c2 = XArray(7, L)       ' col of Supply Code   (row in PREORDER)
            c3 = XArray(8, L)       ' col for retailer names   (row in PREORDER)
            c4 = XArray(9, L)       ' col of Face Values   (row in PREORDER)
            cT = XArray(15, L)      ' col of Type Retailer  (N/A in INV ACC, ORDER FORM or PREORDER)
            B = XArray(16, L)       ' Boolean value to use in orientation  

    If L < 5 Then
                With ActiveWorkbook.Worksheets(Sh)
                    Set Range1 = .Range(.Cells(r1, c1), .Cells(rL, cL))             ' Entire range to be sorted 
                    Set Range2 = .Range(.Cells(r1, c2), .Cells(rL, c2))             ' supply code
                    Set Range3 = .Range(.Cells(r1, c3), .Cells(rL, c3))             ' retailer name
                    Set Range4 = .Range(.Cells(r1, c4), .Cells(rL, c4))             ' product value
                    Set Range5 = .Range(.Cells(r1, cT), .Cells(rL, cT))             ' Type of retailer
                End With
    orient = 2      ' top to bottom
    End If
    
    If L = 5 Then
                With ActiveWorkbook.Worksheets(Sh)
                    Set Range1 = .Range(.Cells(c1, r1), .Cells(cL, rL))             ' Entire range to be sorted  
                    Set Range2 = .Range(.Cells(c2, r1), .Cells(c2, rL))             ' supply code
                    Set Range3 = .Range(.Cells(c3, r1), .Cells(c3, rL))             ' retailer name
                    Set Range4 = .Range(.Cells(c4, r1), .Cells(c4, rL))             ' product value
                End With
    orient = 1      'left to right
    End If
 
Select Case S
        Case 1   ' Sort by Name, product Value only
                ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Clear
                 ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Add Key:=Range3,  SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
                 ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Add Key:=Range4,  SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
    
                With ActiveWorkbook.Worksheets(Sh).Sort
                    .SetRange Range1
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = orient
                    .SortMethod = xlPinYin
                    .Apply
                End With

Two other cases follow with the same structure.

[p45cal]

There's a lot there to try and reproduce the error, especially as I don't know what's in XArray.
However, I do notice two lines and especially their comments:
orient = 2 ' top to bottom
orient = 1 'left to right

When I enumerate xlTopToBottom in code generated when recording a sort operation I get the value 1. Could this be a cause of the problem?

[Kerry H]

Microsoft says "[orientation] Specifies if the sort should be by row (default) or column. Set xlSortColumns value to 1 to sort by column. Set xlSortRows value to 2 to sort by row. (This is the default value.)" I'll try it the other way round!

[p45cal]

I await with bated breath…

[Kerry H]

By Jove it works! Thank you, thank you, p45cal. Perhaps I didn't understand what "sort by rows" means. However, the default orientation is TopToBotton which to me means sorting by rows. We here in the colonies do have a fairly good grip on the English language. Your help is much appreciated.