Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transpose 1 column of 100 items to 10 x 10 array | Excel Discussion (Misc queries) | |||
need formula to count non-zero items in an array based on a vlooku | Excel Worksheet Functions | |||
create an array with unique items IN MEMORY | Excel Worksheet Functions | |||
Counting items in an array | Excel Programming | |||
Number of Items in an Array. | Excel Programming |