Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving a row based on the time (2007)

    Help! I lost all of my macro's when my computer crashed and now I am have to recreate them. The macro I am working on formats data that comes from a pull from our data warehouse. I need to move some rows based on the time given in a cell. The data load gives me the date and time all in one cell. I need to move any row that has a start time of 0:00 or an end time of 23:59 to a new spreadsheet as well as any shift that is over 8 hours in length. I have the code to create the new sheet all set. I just need help with the code to identify the times that need moved.

    I have the code in the attached spreadsheet that I have used before but it is not moving any cells except the ones over 8 hours. Can anyone ID the problem?

    Thanks in advance for any assistance that can be provided.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a row based on the time (2007)

    Sorry about that. Clicked the wrong button and forgot to attach the file.
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Moving a row based on the time (2007)

    In the sheet in your sample workbook, start time is column G and end time is column H, but your code uses

    Const strBegTmCol = "H"
    Const strEndTmCol = "J"

    Next time, make sure that you have backup copies!

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a row based on the time (2007)

    Hans,

    I uploaded the incorrect workbook the first time. There were some steps that needed to be taken first which allowed the constants to be correct. I have modified the workbook to show you what it should be at this point the code is looking at the time and moving the row. This is only one section of a much longer macro that does many other things to the spreadsheet.

    Thanks in advance!
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Moving a row based on the time (2007)

    The cells in columns H and J are formatted to display only the time, but their values are copies of the date+time values in columns G and I, respectively. So although you may see 12:00 AM or 11:59 PM, none of the cells actually contains that value - they contain 03/08/08 12:00 AM or 03/08/08 11:59 PM.

    To take this into account, you can use these lines:

    If Format(wshSource.Range(strBegTmCol & i), "hh:mm AM/PM") = "12:00 AM" Then

    and

    If Format(wshSource.Range(strEndTmCol & i), "hh:mm AM/PM") = "11:59 PM" Then

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a row based on the time (2007)

    Thank you Hans. This worked beautifully!
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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