"orientaion" parameter in vba SORT

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

Code:

---

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.

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).

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. :banghead:

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

Just going out. will get back to you.

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.

Code:

---

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.

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?

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!

I await with bated breath…

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.