Custom sorting, Excel VBA

[cllpmc]

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

 

[APElliott]

Hi there,

I come across this problem a lot!

You have done better than me.

I get your second answer in Excel too.

The way I get around it (which you've proberly tried) is to change the references ie

1Z01
1Z02
1Z03
1Z04
1Z05
1Z06
1Z07
1Z08
1Z09
1Z10
etc

Excel to my understanding sorts by the first character, then 2nd, then 3rd therefore 1Z1 is first then 1Z10 2nd and so on!

Hope this help!

Let me know, it will be the first time I,ve given something from this site, rather than taking all the time!

Regards,

Andrew

 

[cllpmc]

Thanks Andrew,
I am aware that the normal sort-method will not do - thats why I made my own precribed sort-list.
Your workaround will do, but to my use not satisfying.
Still I don't understand why Excel will, but VBA will not.
Regards Claus

 

[cllpmc]

Add this:
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