PDA

View Full Version : How can sort Ascending for data in a Cell



parscon
03-15-2012, 02:25 AM
I have a cell (A1) and i have also some data like (1-4-3-dog-5-2-12-40-50-cat-)
How can i sort by Ascending for a data on cell with macro ?

Thank you .

mancubus
03-15-2012, 03:31 AM
Sub Sort_Cell_Contents()
'ADOPTED FROM:
'http://www.ozgrid.com/forum/showthread.php?t=78972

Dim cll As Range, rng As Range
Dim delim As String

Set rng = Range("A2:G100") 'change to actual range
delim = "-" 'change to actual delimiter

Application.EnableEvents = False
For Each cll In rng
With cll
.Value = Trim(.Value)
If Right(.Value, 1) = delim Then
.Value = Left(.Value, Len(.Value) - 1)
End If
If InStr(.Value, delim) > 0 Then
x = Split(.Value, delim)
SortA x, 0, UBound(x)
.Value = Join(x, delim)
End If
End With
Next
Application.EnableEvents = True

End Sub




Private Sub SortA(ary, LB, UB)

Dim M As Variant, temp
Dim i As Long, ii As Long

i = UB: ii = LB
M = ary(Int((LB + UB) / 2))

Do While ii <= i
Do While Val(ary(ii)) < Val(M)
ii = ii + 1
Loop
Do While Val(ary(i)) > Val(M)
i = i - 1
Loop
If ii <= i Then
temp = ary(ii): ary(ii) = ary(i): ary(i) = temp
ii = ii + 1: i = i - 1
End If
Loop

If LB < i Then SortA ary, LB, i
If ii < UB Then SortA ary, ii, UB

End Sub