Page 1 of 3 123 LastLast
Results 1 to 15 of 37

Thread: Schedule Report

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Please see the attached, I would like a report that looks like Sheet 2. Any ideas?
    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Can you get hold of the source data in a database-like format? The present format in Sheet1 is not very suitable for processing.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797165' date='09-Oct-2009 15:07']Can you get hold of the source data in a database-like format? The present format in Sheet1 is not very suitable for processing.[/quote]

    Thanks Hans. Unfortunately, the source data is in the form of an excel spreadsheet. What you see in sheet 1 is exactly the way the spacing/rows/columns are in the source worksheet...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jlkirk' post='797166' date='09-Oct-2009 22:09']Thanks Hans. Unfortunately, the source data is in the form of an excel spreadsheet. What you see in sheet 1 is exactly the way the spacing/rows/columns are in the source worksheet...[/quote]
    In that case, I'd leave it exactly as it is...

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797167' date='09-Oct-2009 15:12']In that case, I'd leave it exactly as it is...[/quote]

    I really need to have it summarized as proposed-a lot of data that gets very confusing...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Good luck with it!

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='jlkirk' post='797166' date='09-Oct-2009 21:09']Thanks Hans. Unfortunately, the source data is in the form of an excel spreadsheet. What you see in sheet 1 is exactly the way the spacing/rows/columns are in the source worksheet...[/quote]

    Can you explain how the source worksheet is created, there must be some way to produce it in a more usable form.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='797171' date='09-Oct-2009 16:24']Can you explain how the source worksheet is created, there must be some way to produce it in a more usable form.[/quote]

    Hi Stuart, thanks for your reply. The source worksheet is produced by a software program we use in our business. I do not know what language it is, nor would I have access to it. The program is capable of developing reports such as this in either Excel of pdf form. Does that help? I thought perhaps this might be a refinement of Hans' recommended language re the extracted data...

  9. #9
    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
    Does this do what you want?

    Test on a copy!!!

    Steve

    [codebox]Sub SchedConverter()
    Dim rng As Range
    Dim rCell As Range
    Set rng = Columns("A:A").SpecialCells(xlCellTypeConstants)

    For Each rCell In rng
    With rCell.Offset(0, 1)
    .Value = Mid(rCell, 14, 7)
    .Font.Underline = xlUnderlineStyleSingle
    End With
    Next
    Columns("U:U").Delete
    Columns("I:S").Delete
    Columns("C:G").Delete
    Columns("B:B").SpecialCells _
    (xlCellTypeBlanks).EntireRow.Delete
    For Each rCell In rng
    rCell.EntireRow.Insert
    Next
    Rows(1).Delete
    Columns("A:A").Delete
    Set rng = Nothing
    Set rCell = Nothing
    End Sub[/codebox]

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='797181' date='09-Oct-2009 17:14']Does this do what you want?

    Test on a copy!!!

    Steve

    [codebox]Sub SchedConverter()
    Dim rng As Range
    Dim rCell As Range
    Set rng = Columns("A:A").SpecialCells(xlCellTypeConstants)

    For Each rCell In rng
    With rCell.Offset(0, 1)
    .Value = Mid(rCell, 14, 7)
    .Font.Underline = xlUnderlineStyleSingle
    End With
    Next
    Columns("U:U").Delete
    Columns("I:S").Delete
    Columns("C:G").Delete
    Columns("B:B").SpecialCells _
    (xlCellTypeBlanks).EntireRow.Delete
    For Each rCell In rng
    rCell.EntireRow.Insert
    Next
    Rows(1).Delete
    Columns("A:A").Delete
    Set rng = Nothing
    Set rCell = Nothing
    End Sub[/codebox][/quote]

    Thanks for your reply Steve. I tried your code, and got a runtime error 1004 "No cells were found". When I ran the debugger, the following line of code was hi-lited:

    rng = Columns("A:A").SpecialCells(xlCellTypeConstants)

    Any ideas?

    Thanks again.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The error message means that the worksheet you tried it on has a different structure than the worksheet that you posted. The worksheet that you posted has data in column A.

  12. #12
    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
    It means that the cells in column A do not have constants like your example. They are either all blank or have formulas and not values...

    It works fine on your example file, which is the only thing we have to go on...

    Steve

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='797293' date='10-Oct-2009 17:58']It means that the cells in column A do not have constants like your example. They are either all blank or have formulas and not values...

    It works fine on your example file, which is the only thing we have to go on...

    Steve[/quote]

    OK,
    Just so we're all on the same page-here is the original uploaded file with Steve's code...see for yourself...

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You attached two identical workbooks, neither of which contains a macro. I deleted one of them.

    (I don't see any difference with the workbook you originally attached)

  15. #15
    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
    And here is a copy of the file you attached with the code after the code is run...

    Are you sure you had sheet1 active when the code was run?

    You would get that runtime error if sheet2 was the active sheet as nothing is in column A in that sheet...

    Steve
    Attached Files Attached Files

Page 1 of 3 123 LastLast

Posting Permissions

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