Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rearrange colums (2002/SP3)

    I have an array that contains a list of names, for example: ORB160, WP_ELEC, S_ELEC, EE

    I have a VBA code that first sorts the array in ascending order, then does some data muniplulation, and finally output the results to a worksheet. The output headings contain the array list, for example:

    B1 C1 D1 E1

    A1 EE ORB160 S_ELEC WP_ELEC

    Note that the headings are sorted.

    What I want to do is re-arrange the data in each column based on the original list of names in the arrays. In this example, this would be:

    B1 C1 D1 E1

    A1 ORB160 WP_ELEC S_ELEC EE

    So the solution would be to move column E to B, and then move column C to D. However, I'm looking for a more generic VBA to do this since the order and the list of names in the array can change.

    Can someone provide me with some help on getting this done?

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

    Re: Rearrange colums (2002/SP3)

    You could store the original sort order in another array:
    1 2 3 4
    When you sort the array of names, perform the same steps on this array. It ends up
    2 4 3 1
    When you're done, sort this array in ascending order, and perform the same steps on the array of names (or on the columns). In the end, the column headings are back in the original order.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rearrange colums (2002/SP3)

    The problem is that the array (containing the list of names) and the data (values) in the columns are put together in the worksheet, and not in a VBA array, ie, I don't store the data in another array. See an example worksheet. I need to re-arrange the columns based on a specific heading list.
    Attached Files Attached Files

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

    Re: Rearrange colums (2002/SP3)

    That shouldn't matter, but since you haven't given any details of what you're doing, I can't provide more specific help. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  5. #5
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rearrange colums (2002/SP3)

    You might make an Excel>Options>Custom List of the headers in the order that you want.
    Then sort (left to right) on that custom list.

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rearrange colums (2002/SP3)

    Attached is the worksheet and the VBA code that would produce the table shown in Sheet1. What I'd like to do is re-order the table based on the order that is specified the file "Nodes_of_Interest.txt".
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rearrange colums (2002/SP3)

    Any questions/comments?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rearrange colums (2002/SP3)

    If I understand what you are doing you can try these changes and additions.

    Delete the lines as there is no reason to sort the array if you want them in the "node of interest" order:
    <pre>' Sort array in order to bring together same submodel names
    SortInfo DataArray</pre>


    At the red lines in the listed spot
    This sorts the items horizontally based on a row added for sorting
    <pre> ReadTemperatureFromFiles DataArray, strFolder, strFile, f, blnNeedTime
    <font color=red> Range("A1").CurrentRegion.Sort _
    Key1:=Range("A1"), Order1:=xlAscending, _
    Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    Range("a1").EntireRow.Delete
    Cells.EntireColumn.AutoFit</font color=red>
    ExitHandler:</pre>


    Change the line to start 1 row down to allow for sorting row
    <pre> R = <font color=red>2</font color=red></pre>


    Add the line for the 0 column for sorting
    <pre> blnNeedTitle = True
    <font color=red> ActiveCell.Cells(R - 1, 1) = 0</font color=red>
    ActiveCell.Cells(R + 1, 1) = "Time"</pre>


    Add and edit the following code to put in the sorting row and put the data 1 row down
    <pre> If blnNeedTitle = True Then
    <font color=red> ActiveCell.Cells(1, C + 1) = i</font color=red>
    ActiveCell.Cells(<font color=red>2</font color=red>, C + 1) = Trim(SubModelName) & _
    "." & DataArray(i).dblNodeNumber
    ActiveCell.Cells(<font color=red>3</font color=red>, C + 1) = DataArray(i).strNodeName
    End If</pre>



    Steve

  9. #9
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rearrange colums (2002/SP3)

    Great, that's what I was looking for. Thanks alot Steve.

Posting Permissions

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