×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Custom Sorting in VBA Excel

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

RE: Custom Sorting in VBA Excel

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

RE: Custom Sorting in VBA Excel

(OP)
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

RE: Custom Sorting in VBA Excel

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

RE: Custom Sorting in VBA Excel

(OP)
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").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

(OP)
Correction to above:

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

RE: Custom Sorting in VBA Excel

(OP)
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

RE: Custom Sorting in VBA Excel

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

RE: Custom Sorting in VBA Excel

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources