Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Issues/sorting (2003)

    I have a spreadsheet used for scheduling that I am trying to do some sorting via array for re-writing the sorted data on another tab.

    The spreadsheet is attached.

    Once the array is populated (via the code below) I need to sort by the values in TheSchedule(24) then TheSchedule(3) then TheSchedule(4)...does that make sense?
    <pre> 'Load the schedule to an array for sorting and filling in the spreadsheet.
    Sheets("schedule").Select
    Range("B3").Select
    Dim TheSchedule() As String
    ReDim TheSchedule(24, 0)
    Dim blnMapperFlag As Boolean
    blnMapperFlag = False
    For x = 1 To 228
    If Len(ActiveCell.Value) < 1 Or ActiveCell.Value = "" Or ActiveCell.Value = "0" Then
    blnMapperFlag = True
    End If
    If Len(ActiveCell.Offset(0, 1)) > 2 Then
    ReDim Preserve TheSchedule(24, UBound(TheSchedule, 2) + 1)
    TheSchedule(1, UBound(TheSchedule, 2)) = ActiveCell.Value 'Employee Number
    TheSchedule(2, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 1).Value 'Name
    TheSchedule(3, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 2).Value
    TheSchedule(4, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 3).Value
    TheSchedule(5, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 5).Value
    TheSchedule(6, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 6).Value
    TheSchedule(7, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 8).Value
    TheSchedule(8, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 9).Value
    TheSchedule(9, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 11).Value
    TheSchedule(10, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 12).Value
    TheSchedule(11, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 14).Value '
    TheSchedule(12, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 15).Value
    TheSchedule(13, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 17).Value '
    TheSchedule(14, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 18).Value
    TheSchedule(15, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 20).Value
    TheSchedule(16, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 21).Value

    TheSchedule(17, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 69).Value
    TheSchedule(18, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 71).Value
    TheSchedule(19, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 73).Value
    TheSchedule(20, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 75).Value
    TheSchedule(21, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 77).Value
    TheSchedule(22, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 79).Value
    TheSchedule(23, UBound(TheSchedule, 2)) = ActiveCell.Offset(0, 81).Value

    TheSchedule(24, UBound(TheSchedule, 2)) = blnMapperFlag 'Are they a mapper?
    End If
    ActiveCell.Offset(1, 0).Select
    Next x</pre>


    For the life of me, I have no idea how to get the array to sort. I've seen code for sorting multi-dimensional arrays, but they were for sorting by the Y,and I need mine sorted by the X from TheSchedule(X,Y)

    HELP!!

    I appreciate any pointers.
    Attached Files Attached Files
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array Issues/sorting (2003)

    Instead of filling an array, write the data to the other sheet directly, then use Excel's built-in Sort method - it can sort on up to three columns or rows. It's not only much easier than programming it yourself, it's also much faster.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Issues/sorting (2003)

    It's been a long time, but I ended up transposing the array then sorting it and it worked just fine for me.

    I attached my code...
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

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