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.

"[email protected]" 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

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.
>.
>

https://www.excelforum.com/excel-pro...ml#post3935010
Above is also an old thread.

ArrayList can not sort when mixed data types such like number, string.
e.g
Array("a","b","b",2,11,111)
So above link.

Originally Posted by jindon

ArrayList can not sort when mixed data types such like number, string.

The OP indicated the data was text, so I did not think this would be a concern.

With that said, do you have any idea why this post appeared to me when I viewed the forum's questions? I did not sort, search or anything else that might have given me a filtered list, so I cannot understand why I saw this thread in the first place.

Just pointed out the nature of the ArrayList.

Any problem?

Originally Posted by jindon

Just pointed out the nature of the ArrayList.

Any problem?

No, no problem... I just thought you were telling me something directly as opposed to offering information to future readers.

I'm guessing you don't know why I was able to see a 14 year old thread then. Just out of curiosity, if someone had posted a message to that thread and then deleted it, would the thread show up in the list as having recent activity? That is the only thing I can think of, but I really don't know how forums work at their heart, so all I can do is guess.