Custom Sorting in VBA Excel
Custom Sorting in VBA Excel
(OP)
Hi all,
in a VBA code I am sorting data:
objWorksheet.Range("A8:F250").Sort _
Key1:=objWorksheet.Range("A8"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=6, _
MatchCase:=False, _
Orientation:=xlTopToBottom
The CustomOrder:=6 prescribe sort-order to be:
1Z1
1Z2
1Z3
1Z10
1Z11
This sort-order is working fine from Excel; but from VBA-code the sort result is:
1Z1
1Z10
1Z11
1Z2
1Z3
Why ???
Any help would be appreciated.
Best regards
Claus
in a VBA code I am sorting data:
objWorksheet.Range("A8:F250").Sort _
Key1:=objWorksheet.Range("A8"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=6, _
MatchCase:=False, _
Orientation:=xlTopToBottom
The CustomOrder:=6 prescribe sort-order to be:
1Z1
1Z2
1Z3
1Z10
1Z11
This sort-order is working fine from Excel; but from VBA-code the sort result is:
1Z1
1Z10
1Z11
1Z2
1Z3
Why ???
Any help would be appreciated.
Best regards
Claus





RE: Custom Sorting in VBA Excel
Excel sorts things in order... i.e. by first character then by next character on and on that's why all the "1Z1.."'s are listed before the 1Z2's etc... Sorry that I don't know how to solve your problem!
Best Regards
jproj
RE: Custom Sorting in VBA Excel
Thanks anyway.
I am aware of the standard sort method in Excel. But Excel has a cuctom sort method too, where it is possible to specify a sorting list.
My problem is, that even when I can do the sorting correct in Excel (1Z1, 1Z2, 1Z11, 1Z12) this dosn't work from VBA !
Best regards
cll
RE: Custom Sorting in VBA Excel
If it is sorting correctly in excel, you might just be writing your macro incorrectly (or leaving important code out). Try starting with the original order and record a macro (using the macro recorder) while defining your custom sorting order. I'll look into it and see what I can come up with.
Hope this helps!
jproj
RE: Custom Sorting in VBA Excel
i've tried to copy the code from Excel to VBA and vise versa, but no result.
Here's the Excel makro (works):
Range("A8:F250").Select
Selection.Sort Key1:=Range("A24"), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=6, MatchCase:=False, _ Orientation:=xlTopToBottom
Here's the VBA (works not):
Dim objWorksheet As Object, objWorkBook As Object
Set objWorkBook = ExcelServer.Workbooks.Add("Path to file")
Set objWorksheet = objWorkBook.Sheets(1)
objWorksheet.Range("A8:F250").Select
Selection.Sort Key1:=objWorksheet.Range("A8"), _ Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=6, MatchCase:=False, _ Orientation:=xlTopToBottom
Above are copyed from the codes (except for "Path to file" and _ .
I also tryed (as the first letter shows) to use the range without select.
Still I don't get it.
Regards Claus
RE: Custom Sorting in VBA Excel
Selection.Sort Key1:=Range("A8"), Order1:=xlAscending, _ Header:=xlNo
RE: Custom Sorting in VBA Excel
for me it's a bit strange, but could give someone a clue.
Regards
Claus
RE: Custom Sorting in VBA Excel
RE: Custom Sorting in VBA Excel
no, this was just an example. At the moment my list starts with 0S (but it could be 0A or anyother).
As a workaround I have implemented the code:
ExcelServer.SendKeys "%ki{enter}"
DoEvents
before sorting to make excel sort correct from my list.
Another problem I found is, that the custom sorting list is limited to 254 elements in the list.
Thanks for your interest
Best regards Claus