## Sort and Concatenate

You need to take a column of data, sort it, and concatenate it, using a "," as the delimiter, as shown in Figure 4.14.

SortConcat(Rng) The argument is

Rng—The range of data to be sorted and concatenated.

¡¡j Sortooncat calls another procedure,BubbieSort,that must be included. o

Function example:

Function SortConcat(Rng As Range) As Variant Dim MySum As String, arr1() As String Dim j As Integer, i As Integer Dim cl As Range Dim concat As Variant On Error GoTo FuncFail: 'initialize output concat = 0# 'avoid user issues If Rng.Count = 0 Then Exit Function 'get range into variant variable holding array ReDim arr1(1 To Rng.Count) 'fill array i = 1

Next

'sort array elements

Call BubbleSort(arr1)

'create string from array elements

For j = UBound(arrl) To 1 Step -1 If Not IsEmpty(arr1(j)) Then

MySum = arr1(j) & ", " & MySum End If Next j

'assign value to function concat = Left(MySum, Len(MySum) - 2)

'exit point concat_exit:

Exit Function

'display error in cell

FuncFail:

concat = Err.Number & " - " & Err.Description Resume concat_exit End Function

The following function is the ever-popular BubbleSort, a program used by many to do a simple sort of data:

Sub BubbleSort(List() As String) ' Sorts the List array in ascending order Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp

First = LBound(List) Last = UBound(List) For i = First To Last - 1 For j = i + 1 To Last

If UCase(List(i)) > UCase(List(j)) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next i End Sub

Figure 4.14

Sort and concatenate a range of variables.

0 0