Macro looping problem.

[grahf72]

I am a co-op student at Delphi and I have been asked to come up with a
macro that will looks at one column of times and then determine which
shift it is and print the shift letter in another column.

The code I am using is this:

Sub fillInShiftColumna()
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
For Each cell In Range("A1:A100")
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 And cell.Value <= timeA2 Then
cell.Offset(0, 1).Value = "A"
End If
Next
End Sub

The macro works but only loops to row 16 and I can't figure out why.
Any help would be greatly appreciated.

 

[Dave O]

I got it to work by adding the line
Range(cell.Address).Select
....just after the line
For Each cell In Range("A1:A100")
.... so it looks like this:
For Each cell In Range("A1:A100")
Range(cell.Address).Select
If cell.Value >= timeB1 And cell.Value <= timeB2 Then

 

[vezerid]

Before the EndIf add an Else which prints something else in the offset.
See how far this goes. This might give you hints as to how far the loop
goes.

HTH
Kostis Vezerides

 

[Guest]

Worked OK for me.

You say it only looped to row 16: is this because there is no shift data in
column B (and hence you think it stops at row 16)?

If so, perhaps the data is "wrong "as there is nothing in the logic to
prevent it looping 100 times.

 

[Dave O]

You could also do it this way:

Sub fillInShiftColumna()
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:A100").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 And cell.Value <= timeA2 Then
cell.Offset(0, 1).Value = "A"
End If
Next
Range("a1").Select
End Sub

 

[grahf72]

I think it has something do with the data for the A shifts. The B's
and C's print out but none of the A's.

 

[vezerid]

Yes, of course... If we keep the times as 23:00 (start) and 06:59
(end), the condition will never hold. I think this is a case where we
want an OR instead of AND.

HTH
Kostis Vezerides

 

[grahf72]

Yes! It works, thanks a bunch for all the help.

Yes, of course... If we keep the times as 23:00 (start) and 06:59
(end), the condition will never hold. I think this is a case where we
want an OR instead of AND.

HTH
Kostis Vezerides

 

[grahf72]

Ok, I have a new problem. The 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. Is there anyway I could ignore the date in the cell
and just look at the numbers when assigning the shifts?