Custom Sorting in VBA Excel

[cll]

Hi all,
in a VBA code I am sorting data:

objWorksheet.Range("A8:F250&quot.Sort _
Key1:=objWorksheet.Range("A8&quot, _
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

 

[jproj]

cll:

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

 

[cll]

Hi jproj
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

 

[jproj]

cll:

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

 

[cll]

Hi jproj,
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&quot.Select
Selection.Sort Key1:=Range("A24&quot, 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&quot
Set objWorksheet = objWorkBook.Sheets(1)

objWorksheet.Range("A8:F250&quot.Select
Selection.Sort Key1:=objWorksheet.Range("A8&quot, _ 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

 

[cll]

Correction to above:

Selection.Sort Key1:=Range("A8&quot, Order1:=xlAscending, _ Header:=xlNo

 

[cll]

I've found that if I do a manual sorting my macro will function thereafter. Or if I do a manual recalculation or even if I just go into the properties dialog in Excel and select OK - then my sortingmacro will work.
for me it's a bit strange, but could give someone a clue.

Regards
Claus

 

[mark47]

Do the cells in the list always start with 1Z??

 

[cll]

Hey mark47,
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