Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time/date problem.
This macro works for the shifts that only include the hours. In the
sheet that the macro will run on the date is included as well (ex. 10/27/06 6:59 AM), when the macro encounters this it lists the shift as an A shift regardless of the time. Is there anyway I could ignore the date in the cell and just look at the numbers when assigning the shifts? Sub fillShifts() Dim timeA1 As Date, timeA2 As Date, timeB1 As Date Dim timeB2 As Date, timeC1 As Date, timeC2 As Date timeB1 = TimeValue("7:00:00 AM") timeB2 = TimeValue("14:59:59 PM") timeA1 = TimeValue("23:00:00 PM") timeA2 = TimeValue("6:59:59 AM") timeC1 = TimeValue("15:00:00 PM") timeC2 = TimeValue("22:59:59 PM") Dim cell As Range Range("a1:a1000").Select For Each cell In Selection.Cells If cell.Value = timeB1 And cell.Value <= timeB2 Then cell.Offset(0, 1).Value = "B" ElseIf cell.Value = timeC1 And cell.Value <= timeC2 Then cell.Offset(0, 1).Value = "C" ElseIf cell.Value = timeA1 Or cell.Value <= timeA2 Then cell.Offset(0, 1).Value = "A" End If Next Range("a1").Select End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time/date problem.
This should do it for you: I added some math that will trim out the
integer portion of the date, leaving just the time. I also added line that will skip any blank cells that might occur. Sub fillShifts() Dim timeA1 As Date, timeA2 As Date, timeB1 As Date Dim timeB2 As Date, timeC1 As Date, timeC2 As Date timeB1 = TimeValue("7:00:00 AM") timeB2 = TimeValue("14:59:59 PM") timeA1 = TimeValue("23:00:00 PM") timeA2 = TimeValue("6:59:59 AM") timeC1 = TimeValue("15:00:00 PM") timeC2 = TimeValue("22:59:59 PM") Dim cell As Range Range("a1:a1000").Select For Each cell In Selection.Cells If cell.Value = 0 Then GoTo Bailout: 'added this line to skip blank cells If cell.Value - Int(cell.Value) = timeB1 And cell.Value - Int(cell.Value) <= timeB2 Then cell.Offset(0, 1).Value = "B" ElseIf cell.Value - Int(cell.Value) = timeC1 And cell.Value - Int(cell.Value) <= timeC2 Then cell.Offset(0, 1).Value = "C" ElseIf cell.Value - Int(cell.Value) = timeA1 Or cell.Value - Int(cell.Value) <= timeA2 Then cell.Offset(0, 1).Value = "A" End If Bailout: Next Range("a1").Select End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time/date problem.
Thank you very much.
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time/date problem.
No worries... did it work?.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |