PDA

View Full Version : "orientaion" parameter in vba SORT



Kerry H
04-11-2019, 09:50 AM
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
04-11-2019, 11:51 AM
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
04-11-2019, 01:33 PM
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:

p45cal
04-11-2019, 01:34 PM
Show us your code as it is and we'll tweak.

Kerry H
04-11-2019, 01:56 PM
Just going out. will get back to you.

Kerry H
04-12-2019, 07:01 AM
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
04-12-2019, 07:24 AM
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
04-12-2019, 07:49 AM
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
04-12-2019, 07:54 AM
I await with bated breath…

Kerry H
04-12-2019, 08:02 AM
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.