How to Sort This Data

[LadyAziera]

hello guys..

I have a problem in sorting the excel data in VB..This is example of the data:

PAP03-101-108-01-00-00-00-0000
PAP03-101-108-02-00-00-00-0000
PAP04-101-108-01-01-00-00-0000
PAP04-101-108-02-01-00-00-0000
PAP04-101-108-01-02-00-00-0000

When i try to sort the data, the result is:

PAP03-101-108-01-00-00-00-0000
PAP03-101-108-02-00-00-00-0000
PAP04-101-108-01-01-00-00-0000
PAP04-101-108-01-02-00-00-0000
PAP04-101-108-02-01-00-00-0000

Actually, Im trying to sort it to be in this way :

PAP03-101-108-01-00-00-00-0000
PAP04-101-108-01-01-00-00-0000
PAP04-101-108-01-02-00-00-0000
PAP03-101-108-02-00-00-00-0000
PAP04-101-108-02-01-00-00-0000

I donno how to do it.. I try to use macro code, but nothing happened.

Dim tmp1, tmp2 As String
 
     tmp1 = "B2"
     tmp2 = "D" & n
     objExcel.Range("tmp1:tmp2").Select
     objExcel.Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom

help me guyz!

[Ecniv]

It is sorting correctly according to text rules.
If you need to sort differently you'd need a couple of functions to split the cells data into other cells then sort as you require.

[LadyAziera]

thanks 4 the reply!

Am i have to do it in excel form? Or it's better to split it first in the database juz before i display it in excel?

[Webtest]

Would it be possible on the Excel Sheet to split "PAP03-101-108-02-00-00-00-0000" into TWO columns ... "PAP03-" in one, and "101-108-02-00-00-00-0000" in the second? If you can do that, things get a lot easier. You can format the first column Right Justified, and the second column Left Justified to make the data easier to read. Of course, someone is going to come along with some other weird format and foul things up!

[LadyAziera]

thanks Webtest!

It works great!!

[Webtest]

I was thinking that you might also find some advantage in forcing the right hand column into a number and formatting it:
Selection.NumberFormat = "0000-00-0000"
... but Excel only supports 15 significant digits, and you have 18. Oh well, so much for that brainstorm.

[Webtest]

I was thinking that you might also find some advantage in forcing the right hand column into a number and formatting it 'Custom' (manually or programmatically):
Selection.NumberFormat = "000-000-00-00-00-00-0000"
... but Excel only supports 15 significant digits, and you have 18. Oh well, so much for that brainstorm.