Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <P ID="edit" class=small>Edited by TroyWells on 09-Dec-01 19:08.</P>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

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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:
    <pre>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.
    Do While values(hi) >= med_value
    hi = hi - 1
    If hi <= lo Then Exit Do
    Loop

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

    ' Swap the lo and hi values.
    values(lo) = values(hi)

    ' Look up from lo for a value >= med_value.
    lo = lo + 1
    Do While values(lo) < med_value
    lo = lo + 1
    If 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
    </pre>

    Hope that's of some help.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    GoTo InextI
    ElseIf 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
    GoTo InextI
    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
    GoTo InextI
    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

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    Troy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •