Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro looping problem.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro looping problem.
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro looping problem.
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. " wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro looping problem.
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro looping problem.
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro looping problem.
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 wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro looping problem.
Yes! It works, thanks a bunch for all the help.
On Oct 19, 1:52 pm, "vezerid" wrote: 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 wrote: 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.- Hide quoted text -- Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro looping problem.
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel application.quit in macro problem | New Users to Excel | |||
Problem with Before_Save Macro | Excel Discussion (Misc queries) | |||
deleting a macro resulted in a problem | Excel Discussion (Misc queries) | |||
Circular Problem needs Macro | Excel Discussion (Misc queries) | |||
Problem executing a macro from different workbook where it is | Excel Discussion (Misc queries) |