Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re-formating data in tables. (excel 2000)

    Hello all,

    I am trying to create a macro that will allow me to re-order a table thats in row form into a table in column form so that I can create another macro that will allow me to mismatch any data to other data within the whole table. See attachment for a better sense of what I'm trying to do.

    Thanks in advance - LaMont

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Re-formating data in tables. (excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> LaMont

    OK I could not open the workbook for some reason. I downloaded it 3 times, and I could not open any of them.

    But if you mean Transpose when you say: "re-order a table thats in row form into a table in column form" well check the PasteSpecial Transpose = True in the Online help.

    Also you may want to check the workbook you uploaded, I am using XL 2003 and it will not open. Maybe its on my end, I will check.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Re-formating data in tables. (excel 2000)

    Don't know why it will not open. Does anyone else have this problem?

  4. #4
    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: Re-formating data in tables. (excel 2000)

    Try this code. Change the input, output sheet as appropriate and the starting ranges for the input cell and output cell.
    I did not "trim" the data (except to check for END) and I also used a ucase incase it is not always lowercase. though you could trim it if desired.


    I don't understand your "implied question" for the next stage of the macro or even what the "goal" of the project is, so I won't comment on anything further.

    Steve

    <pre>Option Explicit
    Sub Frogmand()
    Dim wksInput As Worksheet
    Dim wksOutput As Worksheet
    Dim rngStartIn As Range
    Dim rngStartOut As Range
    Dim lOffset As Long
    Dim sEnd As String
    Dim lRowOffset As Long
    Dim iColOffset As Integer

    'Change these as appropriate
    sEnd = "END"
    Set wksInput = Worksheets("Sheet1")
    Set rngStartIn = wksInput.Range("A6")
    Set wksOutput = Worksheets("Sheet2")
    Set rngStartOut = wksOutput.Range("A1")

    wksOutput.Cells.ClearContents
    lOffset = 0
    lRowOffset = 0
    Do While rngStartIn.Offset(lOffset, 0) <> ""
    iColOffset = 0
    Do While UCase(Trim(rngStartIn.Offset(lOffset, 0))) <> sEnd
    rngStartOut.Offset(lRowOffset, iColOffset) = _
    rngStartIn.Offset(lOffset, 0)
    iColOffset = iColOffset + 1
    lOffset = lOffset + 1
    Loop
    lOffset = lOffset + 1
    lRowOffset = lRowOffset + 1
    Loop
    End Sub</pre>


  5. #5
    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: Re-formating data in tables. (excel 2000)

    I opened it fine.

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Re-formating data in tables. (excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> LaMont

    Got it open, finally!!! Don't ask what I did, but it worked.

    OK the transpose will help you. Here is what I recorded for you:

    Sub TransposeMyData()
    Range("A27:C31").Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Cells.EntireColumn.AutoFit
    End Sub

    Now this is based on the Range that I could see, what is going to happen is that its going to out the output in this fashion

    Contingency '1-ADAMS-120 to BUNCE-120 1'
    Open branch from bus 336 to bus 373 ckt 1
    Open branch from bus 353 to bus 373 ckt 1

    Then you need to test where it ends, the first one in the first column, and place your end.

    Then move over to the second column and cut and paste the contents and place them in Column A

    To test how many columns you have you can do Range.Columns.Count or Range("A1").end(xlToRight).column.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Re-formating data in tables. (excel 2000)

    Thanks Steve, that works perfectly. All that I am trying to do with the otner macro is have an ability to combine the contingency data fields (the open branch data that was put in its own column by the first macro) with other contingency data fields. So all I would have to do is select mulitple contigencies from a list of the contigencies (probably from a validation list) and have the macro put the the selected contingency's data back into its previous form (the way they were set before the first macro), but in its combined form. The way this data is formed (the form of it before the first macro and after the second being the same) serve the purpose of being a programming language for an engineering tool I use. I'm just trying to manipulate or change the data around easily using macros rather than programming it word for word. I'll try to do what I can with the second macro and I'll probably be on here again concerning how I can do some things with that macro to accomplish what I'm trying to accomplish, but if you or anyone else understand what I am trying to do please feel free to give me any suggetions.

    Much thanks in advance, LaMont

Posting Permissions

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