Moving a row based on the time (2007)

Topic

New Reply

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.

Reply | Quote

Replies

Sorry about that. Clicked the wrong button and forgot to attach the file.

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

Thank you Hans. This worked beautifully!

Reply | Quote