Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
alphabetically sorting an array
Hello!
I want to sort alphabetically a multi-column, bi-dimentional array. I would like to do it all through VBA, not sending the array to a worksheet. Also, if it were possible to specify what column I want to sort, it would be fantastic. Thanking you in advance, Albert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
alphabetically sorting an array
Here is some code I have modified to do it.
The second procedure shows how to call the quicksort. Sub QuickSort(SortArray, col, L, R, bAscending) ' 'Originally Posted by Jim Rech 10/20/98 Excel.Programming 'Modified to sort on first column of a two dimensional array 'Modified to handle a second dimension greater than 1 (or zero) 'Modified to sort on a specified column in a 2D array 'Modified to do Ascending or Descending Dim i, j, X, Y, mm i = L j = R X = SortArray((L + R) / 2, col) If bAscending Then While (i <= j) While (SortArray(i, col) < X And i < R) i = i + 1 Wend While (X < SortArray(j, col) And j L) j = j - 1 Wend If (i <= j) Then For mm = LBound(SortArray, 2) To UBound(SortArray, 2) Y = SortArray(i, mm) SortArray(i, mm) = SortArray(j, mm) SortArray(j, mm) = Y Next mm i = i + 1 j = j - 1 End If Wend Else While (i <= j) While (SortArray(i, col) X And i < R) i = i + 1 Wend While (X SortArray(j, col) And j L) j = j - 1 Wend If (i <= j) Then For mm = LBound(SortArray, 2) To UBound(SortArray, 2) Y = SortArray(i, mm) SortArray(i, mm) = SortArray(j, mm) SortArray(j, mm) = Y Next mm i = i + 1 j = j - 1 End If Wend End If If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending) If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending) End Sub ' sample code that calls it. Sub aaTesterSort() Dim bAscending As Boolean Set rng = Range("I7").CurrentRegion vArr = rng.Value bAscending = False QuickSort vArr, 5, LBound(vArr, 1), UBound(vArr, 1), bAscending Range("I26").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value = vArr End Sub -- Regards, Tom Ogilvy "Albert" wrote: Hello! I want to sort alphabetically a multi-column, bi-dimentional array. I would like to do it all through VBA, not sending the array to a worksheet. Also, if it were possible to specify what column I want to sort, it would be fantastic. Thanking you in advance, Albert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
alphabetically sorting an array
Yes sir, it works fine, except for some strange glitch. The sort seems to
consider uppercase and lowercase as different. What to do? Thanks, Albert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
alphabetically sorting an array
Albert wrote:
Yes sir, it works fine, except for some strange glitch. The sort seems to consider uppercase and lowercase as different. What to do? Thanks, Albert I don't know whether this response will advance or retard your efforts, but the following code snippet may suggest an approach for you, or for someone else who is inclined to help. Or it may prompt someone to suggest an obvious and simple approach that makes this response seem silly. In any event, the code snippet below is from a function that has an optional Boolean parameter, "Match Case", whose default value is False. I believe that if similar such snippets were sprinkled into Tom Ogilvey's code at appropriate places, the sort could be case sensitive or not, as desired, but I'm not currently up to studying my old code to figure it out. If you were to download the freely downloadable file at http://home.pacbell.net/beban and scroll through the functions in it by finding the term "MatchCase", you might get a feel for how to apply such a snippet. For i = LBound(arr) To UBound(arr) If MatchCase Then If arr(i, FilterColumn) < FilterValue Then outputArrayRFilter(w) = RowVector(arr, i) w = w + 1 End If Else If StrComp(arr(i, FilterColumn), FilterValue, vbTextCompare) < 0 Then outputArrayRFilter(w) = RowVector(arr, i) w = w + 1 End If End If Next Alan Beban |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
alphabetically sorting an array
Just to chime in, the code is generalized to handle both text and numbers.
If you want to tailor it to Text and make it case insensitive, you could use the strcomp function with vbTextCompare. Easier might be to put the unchanged routine in a separate module with Option Compare Text at the top. I haven't tested it, but perhaps that will achieve your required output. -- Regards, Tom Ogilvy "Alan Beban" wrote: Albert wrote: Yes sir, it works fine, except for some strange glitch. The sort seems to consider uppercase and lowercase as different. What to do? Thanks, Albert I don't know whether this response will advance or retard your efforts, but the following code snippet may suggest an approach for you, or for someone else who is inclined to help. Or it may prompt someone to suggest an obvious and simple approach that makes this response seem silly. In any event, the code snippet below is from a function that has an optional Boolean parameter, "Match Case", whose default value is False. I believe that if similar such snippets were sprinkled into Tom Ogilvey's code at appropriate places, the sort could be case sensitive or not, as desired, but I'm not currently up to studying my old code to figure it out. If you were to download the freely downloadable file at http://home.pacbell.net/beban and scroll through the functions in it by finding the term "MatchCase", you might get a feel for how to apply such a snippet. For i = LBound(arr) To UBound(arr) If MatchCase Then If arr(i, FilterColumn) < FilterValue Then outputArrayRFilter(w) = RowVector(arr, i) w = w + 1 End If Else If StrComp(arr(i, FilterColumn), FilterValue, vbTextCompare) < 0 Then outputArrayRFilter(w) = RowVector(arr, i) w = w + 1 End If End If Next Alan Beban |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
alphabetically sorting an array
This is kinda weird: my array has 13 columns. The sort works fine for columns
1 to 4, but when I ask it to sort column 5 or more, it sorts column 4 again... What to do? Thanx in advance, Albert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Alphabetically | Excel Worksheet Functions | |||
sorting using letters , but not alphabetically. | Excel Worksheet Functions | |||
sorting multiple columns alphabetically | Excel Discussion (Misc queries) | |||
Sorting columns alphabetically | Excel Worksheet Functions | |||
Sorting Alphabetically | Excel Discussion (Misc queries) |