Alphabetizing array items
 

I am trying to enter a list of items in an array. How can
I alphabetize the items (sort them) as they are entered
into the array?
For example, lets say I have a list of names in a
worksheet from cells A1 to A4: Mike, John, Dave, Mark.
As I enter these names into an array, NameArr(), I want
them to be in alphabetical order, so once they are all
entered, NameArr(1) is Dave.
I hope this makes sense.
Thanks.

Alphabetizing array items
 

" wrote:
I am trying to enter a list of items in an array. How can
I alphabetize the items (sort them) as they are entered
into the array?
<snip
I want them to be in alphabetical order, so once
they are all entered, NameArr(1) is Dave.

To do this, you can transfer the contents of the range to an array. You can
simply say

NameArr = Range("A1:A4").Value

When you do this, NameArr will be a Variant array (a Variant that holds an
array inside it) and the array will be two-dimensional, for the rows and
columns in the original range.

Once you have the data in hand, you can apply any old sort algorithm you
like.

Below is a solution based on an example of the BubbleSort algorithm
available from Microsoft support. You can use this as a foundation if you
want to do something more complex, like vary the range on which the sort
works, or sort whole rows, or sort more complex names.

Sub SortNames()
Dim NameArr As Variant
Dim i As Integer

NameArr = Range("A1:A4") ' or Range().Value to be explicit
BubbleSortColumn NameArr

For i = LBound(NameArr) To UBound(NameArr)
Debug.Print NameArr(i, 1)
Next i
End Sub

Function BubbleSortColumn(ArrayToSort As Variant)
' adapted from http://support.microsoft.com/Default.aspx?kbid=213818
' so that it works on a 2-D array you get when assigning a
' Range to a Variant array
Dim Temp As Variant
Dim i As Integer
Dim NoExchanges As Integer
' added this variable for 2nd dimension index
Const col As Integer = 1

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 1 To UBound(ArrayToSort, col) - 1

' If the element is greater than the element
' following it, exchange the two elements.
If ArrayToSort(i, col) ArrayToSort(i + 1, col) Then
NoExchanges = False
Temp = ArrayToSort(i, col)
ArrayToSort(i, col) = ArrayToSort(i + 1, col)
ArrayToSort(i + 1, col) = Temp
End If
Next i
Loop While Not (NoExchanges)

End Function

Alphabetizing array items
 

Sort the list while it's in a worksheet (Excel's native
sort is very fast) and then read it into your array.

Tony
-----Original Message-----
I am trying to enter a list of items in an array. How can
I alphabetize the items (sort them) as they are entered
into the array?
For example, lets say I have a list of names in a
worksheet from cells A1 to A4: Mike, John, Dave, Mark.
As I enter these names into an array, NameArr(), I want
them to be in alphabetical order, so once they are all
entered, NameArr(1) is Dave.
I hope this makes sense.
Thanks.
.