Sort Numeric and Alpha Characters

This function takes a mixed range of numeric and alpha characters and sorts them— numerically first and then alphabetically. The result is placed in an array that can be displayed on a worksheet through theuse of an array formula, as shown in Figure 4.15. sorter(Rng)

The argument is

Rng—The range to be sorted. Function example:

Function sorter(Rng As Range) As Variant 'returns an array Dim arr1() As Variant

If Rng.Columns.Count > 1 Then Exit Function arrl = Application.Transpose(Rng) Quicksort arrl sorter = Application.Transpose(arrl) End Function

The function uses the following two procedures to sort the data in the range.

Public Sub QuickSort(ByRef vntArr As Variant, _ Optional ByVal lngLeft As Long = -2, _ Optional ByVal lngRight As Long = -2) Dim i, j, lngMid As Long Dim vntTestVal As Variant

If lngLeft = -2 Then lngLeft = LBound(vntArr) If lngRight = -2 Then lngRight = UBound(vntArr) If lngLeft < lngRight Then lngMid = (lngLeft + lngRight) \ 2 vntTestVal = vntArr(lngMid) i = lngLeft j = lngRight Do

Loop

Loop

Call SwapElements(vntArr, i, j) i = i + 1 j = j - 1 End If Loop Until i > j If j <= lngMid Then

Call QuickSort(vntArr, lngLeft, j) Call QuickSort(vntArr, i, lngRight)

Else

Call QuickSort(vntArr, i, lngRight) Call QuickSort(vntArr, lngLeft, j) End If End If End Sub

Private Sub SwapElements(ByRef vntltems As Variant, ByVal lnglteml As Long, _ ByVal lngItem2 As Long) Dim vntTemp As Variant vntTemp = vntItems(lngItem2) vntItems(lngItem2) = vntltems(lnglteml) vntltems(lnglteml) = vntTemp End Sub

Figure 4.15

Sort a mixed alphanumeric list.

0 0