Sort Left to Right Ascending Help Please

[S.H.A.D.O.]

Hi Everyone,

I have some Code ...

With Application.WorksheetFunction
    ActiveCell.Value = .Text(DN, "00") & "-" _
    & .Text(MyArray(A), "00") & "-" _
    & .Text(MyArray(B), "00") & "-" _
    & .Text(MyArray(C), "00") & "-" _
    & .Text(MyArray(D), "00") & "-" _
    & .Text(MyArray(E), "00")
    ActiveCell.Offset(1, 0).Select
End With

... that Produces a String of 6 Numbers like :-

07-01-25-13-29-05

How can I Adapt the Snippet of Code Above so that it Produces ANY String in Ascending Order ( 01-05-07-13-25-29 ) from Left to Right Please.

Thanks in Advance.
All the Best.
SHADO

 

[Zack Barresse]

Hi there,

Something like this maybe ...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Test_SHADO()
    <SPAN style="color:#00007F">Dim</SPAN> MyArray() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> DN
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> myStr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    DN = 7
    <SPAN style="color:#00007F">ReDim</SPAN> MyArray(4)
    MyArray(0) = 1
    MyArray(1) = 25
    MyArray(2) = 13
    MyArray(3) = 29
    MyArray(4) = 5
    <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> MyArray(5)
    MyArray(5) = DN
    MyArray() = Array_Sort(MyArray)
    <SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(MyArray) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(MyArray)
        myStr = myStr & Format(MyArray(i), "00") & "-"
    <SPAN style="color:#00007F">Next</SPAN> i
    myStr = Left(myStr, Len(myStr) - 1)
    MsgBox myStr
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> Array_Sort(<SPAN style="color:#00007F">ByVal</SPAN> NotSortedArry <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#007F00">'Originally from Colo: http://puremis.net/excel/code/022.shtml</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> vElm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(NotSortedArry) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(NotSortedArry)
        <SPAN style="color:#00007F">For</SPAN> j = i + 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(NotSortedArry)
            <SPAN style="color:#00007F">If</SPAN> NotSortedArry(i) > NotSortedArry(j) <SPAN style="color:#00007F">Then</SPAN>
                vElm = NotSortedArry(j)
                NotSortedArry(j) = NotSortedArry(i)
                NotSortedArry(i) = vElm
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
    Array_Sort = NotSortedArry
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>


HTH

 

[Tazguy37]

I'd suggest using Text to Columns with a "-" delimiter to get each number in its own column. Then sort them left to right, ascending, and put them back together with the CONCATENATE (or &) function.

HTH