Sort cell values

  • Thread starter Thread starter Markus Serbel
  • Start date Start date
M

Markus Serbel

Hi,

I want to sort a cell value like "6, 4, 5, 3, 1, 2"
to get the result "1, 2, 3, 4, 5, 6"
What ist the best way to do this?

TIA

Markus Serbel
 
Markus,

The best way is to use a Custom User-Defined-Function. See the code
below. Can either be used in VBA or as a Worksheet function. If used
as a worksheet function, use it like:

=SortCell(A1, TRUE)

to return

1, 2, 3, 4, 5, 6

The macro TryIt shows how to use it in VBA - assumes the input string
is in cell A1.

Both require Excel2000 or later to function, due to the Split and Join
commands.

HTH,
Bernie
MS Excel MVP

Function SortCell(InCell As Range, myBool As Boolean) As Variant
SortCell = Join(ArrayBubbleSort(Split(InCell.Value, ", "), myBool), ",
")
End Function

Sub TryIt()
MsgBox Join(ArrayBubbleSort(Split(Range("A1").Value, ", "), True), ",
")
End Sub

Function ArrayBubbleSort( _
myArray As Variant, _
Optional Ascending As Boolean) _
As Variant
Dim myTemp As Variant
Dim i As Integer
Dim j As Integer

'Do the sort
For i = LBound(myArray) To UBound(myArray) - 1
For j = i + 1 To UBound(myArray)
If Ascending Then
If myArray(i) > myArray(j) Then
myTemp = myArray(j)
myArray(j) = myArray(i)
myArray(i) = myTemp
End If
Else
If myArray(i) < myArray(j) Then
myTemp = myArray(j)
myArray(j) = myArray(i)
myArray(i) = myTemp
End If
End If
Next j
Next i

'Return the array
ArrayBubbleSort = myArray
End Function
 
Bernie Deitrick said:
The best way is to use a Custom User-Defined-Function. See the code
below. Can either be used in VBA or as a Worksheet function. If used
as a worksheet function, use it like:
Click to expand...

Great, Bernie! Thank you very much.
But there's a little problem.
If there are a values like "1, 5, 10, 2, 19"
the result is "1, 10, 19, 2, 5"

Is there a solution to get "1, 2, 5, 10, 19" ?
(The highest Value will be 29)

Thanky again,
Makrus
 
Markus,

Sorry about that. Use the code below, which forces the values to be
numeric.

HTH,
Bernie
MS Excel MVP

Function ArrayBubbleSort( _
myArray As Variant, _
Optional Ascending As Boolean) _
As Variant
Dim myTemp As Variant
Dim myInt() As Variant
Dim i As Integer
Dim j As Integer

ReDim myInt(LBound(myArray) To UBound(myArray))
For i = LBound(myArray) To UBound(myArray)
myInt(i) = Val(Trim(myArray(i)))
Next i

'Do the sort
For i = LBound(myInt) To UBound(myInt) - 1
For j = i + 1 To UBound(myInt)
If Ascending Then
If myInt(i) > myInt(j) Then
myTemp = myInt(j)
myInt(j) = myInt(i)
myInt(i) = myTemp
End If
Else
If myInt(i) < myInt(j) Then
myTemp = myInt(j)
myInt(j) = myInt(i)
myInt(i) = myTemp
End If
End If
Next j
Next i

'Return the array
ArrayBubbleSort = myInt
End Function

Sub TryIt2()
MsgBox Join(ArrayBubbleSort(Split(Range("A1").Value, ", "), True), ",
")
End Sub

Function SortCell(InCell As Range, myBool As Boolean) As Variant
SortCell = Join(ArrayBubbleSort(Split(InCell.Value, ", "), myBool), ",
")
End Function
 
Back
Top