Take cell value spit on delimiter SORT the ensuing array then Join on delimiter post back

[capson]

I want to get a cell value then spit on a delimiter then SORT the ensuing array then Join the sorted array on the same delimiter then post back the ensuing string to the same cell

e.g I have the cell value

AA|MM|CC|WW|BB|NN

I want it to be

AA|BB|CC|MM|NN

I get type mismatch error here

 For i = LBound(ar) To UBound(ar)

The sorted array is empty, it seems the sorting is deleting the array? ... I am not sure what is going on, I have tried various sorting functions and I get the same error for all of them

Thanks

Sub Test()
Dim ar As Variant
Dim strTemp As String

   ar = Split(Cells(2, "E").Value, "|")
   ar = SortAry(ar)
   
   For i = LBound(ar) To UBound(ar)
      MsgBox ar(i)
   Next
   
   strTemp = Join(ar, "|")
   
   Cells(2, "E").Value = strTemp
   
End Sub

Function SortAry(pvarArray As Variant) As Variant
    Dim i As Long
    Dim iMin As Long
    Dim iMax As Long
    Dim varSwap As Variant
    Dim blnSwapped As Boolean
    
    iMin = LBound(pvarArray)
    iMax = UBound(pvarArray) - 1
    Do
        blnSwapped = False
        For i = iMin To iMax
            If pvarArray(i) > pvarArray(i + 1) Then
                varSwap = pvarArray(i)
                pvarArray(i) = pvarArray(i + 1)
                pvarArray(i + 1) = varSwap
                blnSwapped = True
            End If
        Next
        iMax = iMax - 1
    Loop Until Not blnSwapped
End Function

[jindon]

Insert one line

    Loop Until Not blnSwapped
    SortAry = pvarArray
End Function

[capson]

Yes that did it. Thank you

[stnkynts]

use the infamous bubblesrt function

Sub Test()
Dim ar As Variant
Dim strTemp As String
Dim i As Integer

   ar = BubbleSrt(Split(Cells(2, "E").Value, "|"), True)
   
   For i = LBound(ar) To UBound(ar)
      MsgBox ar(i)
   Next
   
   strTemp = Join(ar, "|")
   
   Cells(2, "E").Value = strTemp
   
End Sub

Public Function BubbleSrt(ArrayIn, Ascending As Boolean)
Dim SrtTemp As Variant
Dim i As Long
Dim j As Long
If Ascending = True Then
  For i = LBound(ArrayIn) To UBound(ArrayIn)
    For j = i + 1 To UBound(ArrayIn)
      If ArrayIn(i) > ArrayIn(j) Then
        SrtTemp = ArrayIn(j)
        ArrayIn(j) = ArrayIn(i)
        ArrayIn(i) = SrtTemp
      End If
    Next j
  Next i
Else
  For i = LBound(ArrayIn) To UBound(ArrayIn)
    For j = i + 1 To UBound(ArrayIn)
      If ArrayIn(i) < ArrayIn(j) Then
        SrtTemp = ArrayIn(j)
        ArrayIn(j) = ArrayIn(i)
        ArrayIn(i) = SrtTemp
      End If
    Next j
   Next i
End If
BubbleSrt = ArrayIn
End Function

[capson]

Thanks stnkynts that cleans things up