Time/date problem.

G

grahf72

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
 
D

Dave O

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro looping problem. 8
Using If Statements and a Time Variable 6
Excel VBA to go to specific sheet & cell in the current workbook not working. 2
Blinking Button 2
Find time difference and display it in hours 1
please help.is it possible re-edit macro to convert text like1530-2200 to time format automatically? 1
Help with Code 1
Pass a time value from Excel to Outlook appointment 2

Top