Sorting an Array 3 times (VBA for Word 97/2000)

Topic

New Reply

Edited by TroyWells on 09-Dec-01 19:08.

I’ve added “MY ATTEMPT” below with the associate problems.

I’ve read a lot about sorting arrays, but it still seems to be no easy feet. Thus, I need help sorting an array 3 times.

The array is 2 dimensional and is named/dimensioned as follows:
(Option Base 1)
ListFormatArray(8,ArrayColumnNumber)

I want to sort in ascending order the values in the second demension
1) by the 7th element in the first dimension
2) within those groupings by the 8th element of the first dimension
3) within those groupings by the 3rd element of the first dimension

I’ve looked at SortArray, but it seems to not be able to sort within a group easily.

An example of the order I hope to achieve is:

Order:
Numbers
No Table
Low Indent
High Indent
NonBordered Table
Low Indent
High Indent
Bordered Table
Low Indent
High Indent
Bullets
No Table
Low Indent
High Indent
NonBordered Table
Low Indent
High Indent
Bordered Table
Low Indent
High Indent

MY ATTEMPT**********************************************************************************
‘I included this section of values for testing purposes only.
ReDim Preserve ListFormatArray(8, 12) As Variant
ListFormatArray(1, 1) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 1) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 1) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 1) = ” Bordered” ‘Table Format
ListFormatArray(5, 1) = “” ‘ComboBox1 value
ListFormatArray(6, 1) = “” ‘ComboBox2 value
ListFormatArray(7, 1) = “0” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 1) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 2) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 2) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 2) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 2) = ” Bordered” ‘Table Format
ListFormatArray(5, 2) = “” ‘ComboBox1 value
ListFormatArray(6, 2) = “” ‘ComboBox2 value
ListFormatArray(7, 2) = “0” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 2) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 3) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 3) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 3) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 3) = ” Bordered” ‘Table Format
ListFormatArray(5, 3) = “” ‘ComboBox1 value
ListFormatArray(6, 3) = “” ‘ComboBox2 value
ListFormatArray(7, 3) = “1” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 3) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 4) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 4) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 4) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 4) = ” Bordered” ‘Table Format
ListFormatArray(5, 4) = “” ‘ComboBox1 value
ListFormatArray(6, 4) = “” ‘ComboBox2 value
ListFormatArray(7, 4) = “1” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 4) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 5) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 5) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 5) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 5) = ” Bordered” ‘Table Format
ListFormatArray(5, 5) = “” ‘ComboBox1 value
ListFormatArray(6, 5) = “” ‘ComboBox2 value
ListFormatArray(7, 5) = “2” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 5) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 6) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 6) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 6) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 6) = ” Bordered” ‘Table Format
ListFormatArray(5, 6) = “” ‘ComboBox1 value
ListFormatArray(6, 6) = “” ‘ComboBox2 value
ListFormatArray(7, 6) = “2” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 6) = “1” ‘0 for numbers and 1 for bullets
‘bullets
ListFormatArray(1, 7) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 7) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 7) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 7) = ” Bordered” ‘Table Format
ListFormatArray(5, 7) = “” ‘ComboBox1 value
ListFormatArray(6, 7) = “” ‘ComboBox2 value
ListFormatArray(7, 7) = “0” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 7) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 8) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 8) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 8) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 8) = ” Bordered” ‘Table Format
ListFormatArray(5, 8) = “” ‘ComboBox1 value
ListFormatArray(6, 8) = “” ‘ComboBox2 value
ListFormatArray(7, 8) = “0” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 8) = “1” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 9) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 9) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 9) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 9) = ” Bordered” ‘Table Format
ListFormatArray(5, 9) = “” ‘ComboBox1 value
ListFormatArray(6, 9) = “” ‘ComboBox2 value
ListFormatArray(7, 9) = “1” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 9) = “1” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 10) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 10) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 10) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 10) = ” Bordered” ‘Table Format
ListFormatArray(5, 10) = “” ‘ComboBox1 value
ListFormatArray(6, 10) = “” ‘ComboBox2 value
ListFormatArray(7, 10) = “1” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 10) = “1” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 11) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 11) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 11) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 11) = ” Bordered” ‘Table Format
ListFormatArray(5, 11) = “” ‘ComboBox1 value
ListFormatArray(6, 11) = “” ‘ComboBox2 value
ListFormatArray(7, 11) = “2” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 11) = “1” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 12) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 12) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 12) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 12) = ” Bordered” ‘Table Format
ListFormatArray(5, 12) = “” ‘ComboBox1 value
ListFormatArray(6, 12) = “” ‘ComboBox2 value
ListFormatArray(7, 12) = “2” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 12) = “1” ‘0 for numbers and 1 for bullets

‘Sort the array by numbers/bullets, then by table type, and then by indent level.
Dim NumberListCount As Integer
Dim TableNoCountNumber As Integer, TableBorderCountNumber As Integer, TableNoBorderCountNumber As Integer
Dim TableNoCountBullet As Integer, TableBorderCountBullet As Integer, TableNoBorderCountBullet As Integer
NumberListCount = 0
TableNoCountNumber = 0
TableBorderCountNumber = 0
TableNoBorderCountNumber = 0
TableNoCountBullet = 0
TableBorderCountBullet = 0
TableNoBorderCountBullet = 0

WordBasic.SortArray ListFormatArray(), 1, ArrayColumnNumber, 0, 8
For N = 1 To ArrayColumnNumber
If ListFormatArray(8, N) = 0 Then NumberListCount = NumberListCount + 1
Next N
WordBasic.SortArray ListFormatArray(), 1, NumberListCount, 1, 7
WordBasic.SortArray ListFormatArray(), NumberListCount + 1, ArrayColumnNumber, 1, 7
For N = 1 To ArrayColumnNumber
If ListFormatArray(8, N) = 0 And ListFormatArray(7, N) = 0 Then
TableNoCountNumber = TableNoCountNumber + 1
ElseIf ListFormatArray(8, N) = 0 And ListFormatArray(7, N) = 1 Then
TableNoBorderCountNumber = TableNoBorderCountNumber + 1
ElseIf ListFormatArray(8, N) = 0 And ListFormatArray(7, N) = 2 Then
TableBorderCountNumber = TableBorderCountNumber + 1
ElseIf ListFormatArray(8, N) = 1 And ListFormatArray(7, N) = 0 Then
TableNoCountBullet = TableNoCountBullet + 1
ElseIf ListFormatArray(8, N) = 1 And ListFormatArray(7, N) = 1 Then
TableNoBorderCountBullet = TableNoBorderCountBullet + 1
ElseIf ListFormatArray(8, N) = 1 And ListFormatArray(7, N) = 2 Then
TableBorderCountBullet = TableBorderCountBullet + 1
End If
Next N
WordBasic.SortArray ListFormatArray(), 1, TableNoCountNumber, 1, 3
WordBasic.SortArray ListFormatArray(), TableNoCountNumber + 1, TableNoCountNumber + TableNoBorderCountNumber, 1, 3
WordBasic.SortArray ListFormatArray(), TableNoCountNumber + TableNoBorderCountNumber + 1, TableNoCountNumber + TableNoBorderCountNumber + TableBorderCountNumber, 1, 3
WordBasic.SortArray ListFormatArray(), TableNoCountNumber + TableNoBorderCountNumber + TableBorderCountNumber + 1, TableNoCountNumber + TableNoBorderCountNumber + TableBorderCountNumber + TableNoCountBullet, 1, 3
WordBasic.SortArray ListFormatArray(), TableNoCountNumber + TableNoBorderCountNumber + TableBorderCountNumber + TableNoCountBullet + 1, TableNoCountNumber + TableNoBorderCountNumber + TableBorderCountNumber + TableNoCountBullet + TableNoBorderCountBullet, 1, 3
WordBasic.SortArray ListFormatArray(), TableNoCountNumber + TableNoBorderCountNumber + TableBorderCountNumber + TableNoCountBullet + TableNoBorderCountBullet + 1, TableNoCountNumber + TableNoBorderCountNumber + TableBorderCountNumber + TableNoCountBullet + TableNoBorderCountBullet + TableBorderCountBullet, 1, 3

MsgBox ListFormatArray(8, 1) & “, ” & ListFormatArray(7, 1) & “, ” & ListFormatArray(3, 1)
MsgBox ListFormatArray(8, 2) & “, ” & ListFormatArray(7, 2) & “, ” & ListFormatArray(3, 2)
MsgBox ListFormatArray(8, 3) & “, ” & ListFormatArray(7, 3) & “, ” & ListFormatArray(3, 3)
MsgBox ListFormatArray(8, 4) & “, ” & ListFormatArray(7, 4) & “, ” & ListFormatArray(3, 4)
MsgBox ListFormatArray(8, 5) & “, ” & ListFormatArray(7, 5) & “, ” & ListFormatArray(3, 5)
MsgBox ListFormatArray(8, 6) & “, ” & ListFormatArray(7, 6) & “, ” & ListFormatArray(3, 6)
MsgBox ListFormatArray(8, 7) & “, ” & ListFormatArray(7, 7) & “, ” & ListFormatArray(3, 7)
MsgBox ListFormatArray(8, 8) & “, ” & ListFormatArray(7, 8) & “, ” & ListFormatArray(3, 8)
MsgBox ListFormatArray(8, 9) & “, ” & ListFormatArray(7, 9) & “, ” & ListFormatArray(3, 9)
MsgBox ListFormatArray(8, 10) & “, ” & ListFormatArray(7, 10) & “, ” & ListFormatArray(3, 10)
MsgBox ListFormatArray(8, 11) & “, ” & ListFormatArray(7, 11) & “, ” & ListFormatArray(3, 11)
MsgBox ListFormatArray(8, 12) & “, ” & ListFormatArray(7, 12) & “, ” & ListFormatArray(3, 12)

What I found is that this doesn’t seem to sort at all. I noticed I was missing a parameter:

SortArray ArrayName[$]() [, Order] [, From] [, To] [, SortType] [, SortKey]

But when I tried to add it (the Order parameter), it get a Bad Parameter error.

Thanks for your help!!
Troy

Reply | Quote

Replies

Troy,

Without going into a detailed code (my apologies, my time is quite limited), my suggestion is that you sort the array yourself manually, and bypass the Wordbasic.sort completely.

Here’s the basic logic I suggest :

blnSorted = false
Repeat until blnSorted
for i = 0 to ubound(arrayElement) – 1
if arrayElement{i) > arrayElement(i + 1) then
(swap arrayElement(i + 1) with arrayElement(i + 1)
exit for ‘ This will restart the “for” loop at the beginning again
end if
if i = ubound(arrayElement) – 1 then
blnSorted = true
end if
next
loop

This is rough; you will need to swap (ot compare) more than one dimension to get it going, but that’s the quick view.

The technique is known as a Bubble Sort- the lower elements eventually bubble down to the bottom of the array.

On a big huge sort, this would not be very efficient- but on the size of the arrayus you’re dealing with, no problem- and it’s easy to code.

Reply | Quote

I agree that a bubblesort will do me just fine and is easier to code than other examples. Thanks!!

HOWEVER, one thing I can’t seem to get is how to swap the rows in the array.

This is a two dimension array where the second dimension are the “rows” to be swapped.

For example, in the example I included, the dimensions are:

ListFormatArray(8, 12)

So:

ListFormatArray(1, I) would swap with ListFormatArray(1, I +1)
ListFormatArray(2, I) would swap with ListFormatArray(2, I +1)

I’ve made some very NON-elegant attempts at this, but it seems the values just get jumbled up. I’ve included the sample of my attempt below:

ReDim Preserve ListFormatArray(8, 12)
ListFormatArray(1, 1) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 1) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 1) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 1) = ” Bordered” ‘Table Format
ListFormatArray(5, 1) = “” ‘ComboBox1 value
ListFormatArray(6, 1) = “” ‘ComboBox2 value
ListFormatArray(7, 1) = “0” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 1) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 2) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 2) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 2) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 2) = ” Bordered” ‘Table Format
ListFormatArray(5, 2) = “” ‘ComboBox1 value
ListFormatArray(6, 2) = “” ‘ComboBox2 value
ListFormatArray(7, 2) = “0” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 2) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 3) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 3) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 3) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 3) = ” Bordered” ‘Table Format
ListFormatArray(5, 3) = “” ‘ComboBox1 value
ListFormatArray(6, 3) = “” ‘ComboBox2 value
ListFormatArray(7, 3) = “1” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 3) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 4) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 4) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 4) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 4) = ” Bordered” ‘Table Format
ListFormatArray(5, 4) = “” ‘ComboBox1 value
ListFormatArray(6, 4) = “” ‘ComboBox2 value
ListFormatArray(7, 4) = “1” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 4) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 5) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 5) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 5) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 5) = ” Bordered” ‘Table Format
ListFormatArray(5, 5) = “” ‘ComboBox1 value
ListFormatArray(6, 5) = “” ‘ComboBox2 value
ListFormatArray(7, 5) = “2” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 5) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 6) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 6) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 6) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 6) = ” Bordered” ‘Table Format
ListFormatArray(5, 6) = “” ‘ComboBox1 value
ListFormatArray(6, 6) = “” ‘ComboBox2 value
ListFormatArray(7, 6) = “2” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 6) = “1” ‘0 for numbers and 1 for bullets
‘bullets
ListFormatArray(1, 7) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 7) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 7) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 7) = ” Bordered” ‘Table Format
ListFormatArray(5, 7) = “” ‘ComboBox1 value
ListFormatArray(6, 7) = “” ‘ComboBox2 value
ListFormatArray(7, 7) = “0” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 7) = “0” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 8) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 8) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 8) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 8) = ” Bordered” ‘Table Format
ListFormatArray(5, 8) = “” ‘ComboBox1 value
ListFormatArray(6, 8) = “” ‘ComboBox2 value
ListFormatArray(7, 8) = “0” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 8) = “1” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 9) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 9) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 9) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 9) = ” Bordered” ‘Table Format
ListFormatArray(5, 9) = “” ‘ComboBox1 value
ListFormatArray(6, 9) = “” ‘ComboBox2 value
ListFormatArray(7, 9) = “1” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 9) = “1” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 10) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 10) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 10) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 10) = ” Bordered” ‘Table Format
ListFormatArray(5, 10) = “” ‘ComboBox1 value
ListFormatArray(6, 10) = “” ‘ComboBox2 value
ListFormatArray(7, 10) = “1” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 10) = “1” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 11) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 11) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 11) = “18” ‘ExistingParagraphIndent
ListFormatArray(4, 11) = ” Bordered” ‘Table Format
ListFormatArray(5, 11) = “” ‘ComboBox1 value
ListFormatArray(6, 11) = “” ‘ComboBox2 value
ListFormatArray(7, 11) = “2” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 11) = “1” ‘0 for numbers and 1 for bullets

ListFormatArray(1, 12) = “0” ‘ExistingNumberStyle 0 through 4
ListFormatArray(2, 12) = “%5)” ‘ExistingNumberFormat
ListFormatArray(3, 12) = “36” ‘ExistingParagraphIndent
ListFormatArray(4, 12) = ” Bordered” ‘Table Format
ListFormatArray(5, 12) = “” ‘ComboBox1 value
ListFormatArray(6, 12) = “” ‘ComboBox2 value
ListFormatArray(7, 12) = “2” ‘Sort number for table format 0 no 1 non 2 bordered
ListFormatArray(8, 12) = “1” ‘0 for numbers and 1 for bullets

ReDim Preserve ListFormatArray(8, UBound(ListFormatArray, 2) + 1)
If UBound(ListFormatArray, 2) > 1 Then
Dim I As Integer
Dim BlnSorted As Boolean
Dim strTemp As String
BlnSorted = False
Do Until BlnSorted
For I = 1 To UBound(ListFormatArray, 2) – 1
If ListFormatArray(8, I) ListFormatArray(8, I + 1) Then

ListFormatArray(1, UBound(ListFormatArray, 2)) = ListFormatArray(1, I)
ListFormatArray(2, UBound(ListFormatArray, 2)) = ListFormatArray(2, I)
ListFormatArray(3, UBound(ListFormatArray, 2)) = ListFormatArray(3, I)
ListFormatArray(4, UBound(ListFormatArray, 2)) = ListFormatArray(4, I)
ListFormatArray(5, UBound(ListFormatArray, 2)) = ListFormatArray(5, I)
ListFormatArray(6, UBound(ListFormatArray, 2)) = ListFormatArray(6, I)
ListFormatArray(7, UBound(ListFormatArray, 2)) = ListFormatArray(7, I)
ListFormatArray(8, UBound(ListFormatArray, 2)) = ListFormatArray(8, I)

ListFormatArray(1, I) = ListFormatArray(1, I + 1)
ListFormatArray(2, I) = ListFormatArray(2, I + 1)
ListFormatArray(3, I) = ListFormatArray(3, I + 1)
ListFormatArray(4, I) = ListFormatArray(4, I + 1)
ListFormatArray(5, I) = ListFormatArray(5, I + 1)
ListFormatArray(6, I) = ListFormatArray(6, I + 1)
ListFormatArray(7, I) = ListFormatArray(7, I + 1)
ListFormatArray(8, I) = ListFormatArray(8, I + 1)

ListFormatArray(1, I + 1) = ListFormatArray(1, UBound(ListFormatArray, 2))
ListFormatArray(2, I + 1) = ListFormatArray(2, UBound(ListFormatArray, 2))
ListFormatArray(3, I + 1) = ListFormatArray(3, UBound(ListFormatArray, 2))
ListFormatArray(4, I + 1) = ListFormatArray(4, UBound(ListFormatArray, 2))
ListFormatArray(5, I + 1) = ListFormatArray(5, UBound(ListFormatArray, 2))
ListFormatArray(6, I + 1) = ListFormatArray(6, UBound(ListFormatArray, 2))
ListFormatArray(7, I + 1) = ListFormatArray(7, UBound(ListFormatArray, 2))
ListFormatArray(8, I + 1) = ListFormatArray(8, UBound(ListFormatArray, 2))
Exit For
ElseIf ListFormatArray(7, I) ListFormatArray(7, I + 1) Then

ListFormatArray(1, UBound(ListFormatArray, 2)) = ListFormatArray(1, I)
ListFormatArray(2, UBound(ListFormatArray, 2)) = ListFormatArray(2, I)
ListFormatArray(3, UBound(ListFormatArray, 2)) = ListFormatArray(3, I)
ListFormatArray(4, UBound(ListFormatArray, 2)) = ListFormatArray(4, I)
ListFormatArray(5, UBound(ListFormatArray, 2)) = ListFormatArray(5, I)
ListFormatArray(6, UBound(ListFormatArray, 2)) = ListFormatArray(6, I)
ListFormatArray(7, UBound(ListFormatArray, 2)) = ListFormatArray(7, I)
ListFormatArray(8, UBound(ListFormatArray, 2)) = ListFormatArray(8, I)

ListFormatArray(1, I) = ListFormatArray(1, I + 1)
ListFormatArray(2, I) = ListFormatArray(2, I + 1)
ListFormatArray(3, I) = ListFormatArray(3, I + 1)
ListFormatArray(4, I) = ListFormatArray(4, I + 1)
ListFormatArray(5, I) = ListFormatArray(5, I + 1)
ListFormatArray(6, I) = ListFormatArray(6, I + 1)
ListFormatArray(7, I) = ListFormatArray(7, I + 1)
ListFormatArray(8, I) = ListFormatArray(8, I + 1)

ListFormatArray(1, I + 1) = ListFormatArray(1, UBound(ListFormatArray, 2))
ListFormatArray(2, I + 1) = ListFormatArray(2, UBound(ListFormatArray, 2))
ListFormatArray(3, I + 1) = ListFormatArray(3, UBound(ListFormatArray, 2))
ListFormatArray(4, I + 1) = ListFormatArray(4, UBound(ListFormatArray, 2))
ListFormatArray(5, I + 1) = ListFormatArray(5, UBound(ListFormatArray, 2))
ListFormatArray(6, I + 1) = ListFormatArray(6, UBound(ListFormatArray, 2))
ListFormatArray(7, I + 1) = ListFormatArray(7, UBound(ListFormatArray, 2))
ListFormatArray(8, I + 1) = ListFormatArray(8, UBound(ListFormatArray, 2))
Exit For
ElseIf ListFormatArray(3, I) ListFormatArray(3, I + 1) Then

ListFormatArray(1, UBound(ListFormatArray, 2)) = ListFormatArray(1, I)
ListFormatArray(2, UBound(ListFormatArray, 2)) = ListFormatArray(2, I)
ListFormatArray(3, UBound(ListFormatArray, 2)) = ListFormatArray(3, I)
ListFormatArray(4, UBound(ListFormatArray, 2)) = ListFormatArray(4, I)
ListFormatArray(5, UBound(ListFormatArray, 2)) = ListFormatArray(5, I)
ListFormatArray(6, UBound(ListFormatArray, 2)) = ListFormatArray(6, I)
ListFormatArray(7, UBound(ListFormatArray, 2)) = ListFormatArray(7, I)
ListFormatArray(8, UBound(ListFormatArray, 2)) = ListFormatArray(8, I)

ListFormatArray(1, I) = ListFormatArray(1, I + 1)
ListFormatArray(2, I) = ListFormatArray(2, I + 1)
ListFormatArray(3, I) = ListFormatArray(3, I + 1)
ListFormatArray(4, I) = ListFormatArray(4, I + 1)
ListFormatArray(5, I) = ListFormatArray(5, I + 1)
ListFormatArray(6, I) = ListFormatArray(6, I + 1)
ListFormatArray(7, I) = ListFormatArray(7, I + 1)
ListFormatArray(8, I) = ListFormatArray(8, I + 1)

ListFormatArray(1, I + 1) = ListFormatArray(1, UBound(ListFormatArray, 2))
ListFormatArray(2, I + 1) = ListFormatArray(2, UBound(ListFormatArray, 2))
ListFormatArray(3, I + 1) = ListFormatArray(3, UBound(ListFormatArray, 2))
ListFormatArray(4, I + 1) = ListFormatArray(4, UBound(ListFormatArray, 2))
ListFormatArray(5, I + 1) = ListFormatArray(5, UBound(ListFormatArray, 2))
ListFormatArray(6, I + 1) = ListFormatArray(6, UBound(ListFormatArray, 2))
ListFormatArray(7, I + 1) = ListFormatArray(7, UBound(ListFormatArray, 2))
ListFormatArray(8, I + 1) = ListFormatArray(8, UBound(ListFormatArray, 2))
Exit For
End If

If I = UBound(ListFormatArray, 2) – 1 Then
BlnSorted = True
End If
InextI:
Next I
Loop
ReDim Preserve ListFormatArray(8, UBound(ListFormatArray, 2) – 1)
End If

MsgBox ListFormatArray(8, 1) & “, ” & ListFormatArray(7, 1) & “, ” & ListFormatArray(3, 1)
MsgBox ListFormatArray(8, 2) & “, ” & ListFormatArray(7, 2) & “, ” & ListFormatArray(3, 2)
MsgBox ListFormatArray(8, 3) & “, ” & ListFormatArray(7, 3) & “, ” & ListFormatArray(3, 3)
MsgBox ListFormatArray(8, 4) & “, ” & ListFormatArray(7, 4) & “, ” & ListFormatArray(3, 4)
MsgBox ListFormatArray(8, 5) & “, ” & ListFormatArray(7, 5) & “, ” & ListFormatArray(3, 5)
MsgBox ListFormatArray(8, 6) & “, ” & ListFormatArray(7, 6) & “, ” & ListFormatArray(3, 6)
MsgBox ListFormatArray(8, 7) & “, ” & ListFormatArray(7, 7) & “, ” & ListFormatArray(3, 7)
MsgBox ListFormatArray(8, 8) & “, ” & ListFormatArray(7, 8) & “, ” & ListFormatArray(3, 8)
MsgBox ListFormatArray(8, 9) & “, ” & ListFormatArray(7, 9) & “, ” & ListFormatArray(3, 9)
MsgBox ListFormatArray(8, 10) & “, ” & ListFormatArray(7, 10) & “, ” & ListFormatArray(3, 10)
MsgBox ListFormatArray(8, 11) & “, ” & ListFormatArray(7, 11) & “, ” & ListFormatArray(3, 11)
MsgBox ListFormatArray(8, 12) & “, ” & ListFormatArray(7, 12) & “, ” & ListFormatArray(3, 12)

Thanks again for your help!!
Troy

Reply | Quote

You could make your element swapping more concise

For k = 1 to 8
ListFormatArray(k, UBound(ListFormatArray, 2)) = ListFormatArray(2, k)
ListFormatArray(k, I) = ListFormatArray(k, I + 1)
ListFormatArray(k, I + 1) = ListFormatArray(k, UBound(ListFormatArray, 2))
Next k

Reply | Quote

Thanks!! After making it more concise, I was able to more easily find an error that was causing it to jumble everything up.

Troy

Reply | Quote

Hi Troy,
I may have completely misunderstood what you’re after but I think that it might be less complicated if you could pull out the 7th, 8th and 3rd elements and concatenate them (you may need to pad them with leading zeroes depending on what could be in them) and then perform one sort in your array based on that concatenated value. That ought, hopefully, to give you the equivalent result but be a lot easier to code! Incidentally, this code from the MSDN, is something I use whenever I need to sort arrays:

Sub Quicksort(values() As String, _
              ByVal min As Long, _
              ByVal max As Long)

  Dim med_value As String
  Dim hi As Long
  Dim lo As Long
  Dim i As Long

  ' If the list has only 1 item, it's sorted.
  If min >= max Then Exit Sub

  ' Pick a dividing item randomly.
  i = min + Int(Rnd(max - min + 1))
  med_value = values(i)

  ' Swap the dividing item to the front of the list.
  values(i) = values(min)

  ' Separate the list into sublists.
  lo = min
  hi = max
  Do
    ' Look down from hi for a value = med_value
      hi = hi - 1
      If hi <= lo Then Exit Do
    Loop

    If hi = med_value.
    lo = lo + 1
    Do While values(lo) = hi Then Exit Do
    Loop

    If lo >= hi Then
      ' The list is separated.
      lo = hi
      values(hi) = med_value
      Exit Do
    End If

    ' Swap the lo and hi values.
    values(hi) = values(lo)
  Loop ' Loop until the list is separated.

  ' Recursively sort the sublists.
  Quicksort values, min, lo - 1
  Quicksort values, lo + 1, max

End Sub

Hope that’s of some help.

Reply | Quote

Please clarify:

“I want to sort in ascending order the values in the second demension
1) by the 7th element in the first dimension
2) within those groupings by the 8th element of the first dimension
3) within those groupings by the 3rd element of the first dimension”

You can sort by columns and you can sort by rows.

I don’t know what it means to, say, sort ” by the 7th element in the first dimension”.

Reply | Quote