Error In My Logic

[diablo]

Hello,
Looking for some more help with spreadsheet code. This spreadsheet that I'm
working on is functioning correctly with the exception of cell "(0,7)". When
the time value in cell "(0,5)" is displayed I want to print "1st Shift" or
"2nd Shift" or "3rd Shift" in cell "(0.7)". Regardless of the time that is
being displayed in "(0.5)" I get "3rd Shift" in cell "(0,7)".

There is apparently some logic error in my code, but I don't see it. Can
anyone lend a hand?

Thanks,
Brian

my code below

=================================

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Target.Count = 1 Then
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 5).Value = Format(Time(), "hh:mm")
Target.Offset(0, 6).Value = Format(Date)
If Target.Offset(0, 5).Value >= TimeValue("07:00:00 AM") <
TimeValue("15:00:00 PM") Then Target.Offset(0, 7) = "1st Shift"
End If
If Target.Offset(0, 5).Value >= TimeValue("15:00:00 PM") <
TimeValue("23:00:00 PM") Then Target.Offset(0, 7) = "2nd Shift"
End If
If Target.Offset(0, 5).Value >= TimeValue("23:00:00 PM") <
TimeValue("07:00:00 AM") Then Target.Offset(0, 7) = "3rd Shift"
End If
If Target.Value = "" Then
Target.Offset(0, 5).ClearContents
Target.Offset(0, 6).ClearContents
Target.Offset(0, 7).ClearContents
End If
errHandler:
Application.EnableEvents = True
End Sub

=================================

 

[Pete_UK]

The syntax should be:

If Target.Offset(0, 5).Value >= TimeValue("07:00:00 AM") _
And Target.Offset(0, 5).Value < TimeValue("15:00:00 PM") _
Then Target.Offset(0, 7) = "1st Shift"

I've split the line to avoid awkward line-wrap. Similar changes are
needed in the lines for 2nd and 3rd shifts.

Hope this helps.

Pete

 

[Dave Peterson]

Sometimes using if/then/elses gets to be pretty confusing. And "Select Case"
comes to the rescue. And putting them in a nice order makes it work nicer, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTime As Date
Dim myStr As String

On Error GoTo errHandler
With Target
If .Cells.Count = 1 Then
If Intersect(.Cells, Me.Range("A:A")) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
If .Value = "" Then
.Offset(0, 5).Resize(1, 3).ClearContents
Else
myTime = Time

With .Offset(0, 5)
.Value = myTime
.NumberFormat = "hh:mm"
End With

With .Offset(0, 6)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With

myStr = ""
Select Case myTime
Case Is < TimeSerial(7, 0, 0), _
Is >= TimeSerial(23, 0, 0)
myStr = "3rd Shift"
Case Is < TimeSerial(15, 0, 0)
myStr = "1st Shift"
Case Is < TimeSerial(23, 0, 0)
myStr = "2nd Shift"
End Select
.Offset(0, 7).Value = myStr
End If
End If
End If
End With

errHandler:
Application.EnableEvents = True
End Sub

 

[robofanuc]

Dave,

I need to look this code over to better understand how it works. But
"works" is the key word. Thanks for showing this to me, it works great.

Thanks again,
Brian

 

[robofanuc]

Pete,

I used the code the way that you showed me, it works up to the "2nd
shift" string. But will put nothing in the cell for times that fall
during 3rd shift.

Here's how I used it:

================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Target.Count = 1 Then
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 6).Value = Format(Date)
Target.Offset(0, 5).Value = Format(Time(), "hh:mm")
If Target.Offset(0, 5).Value >= TimeValue("07:00:00 AM") _
And Target.Offset(0, 5).Value < TimeValue("15:00:00 PM") _
Then Target.Offset(0, 7) = "1st Shift"
If Target.Offset(0, 5).Value >= TimeValue("15:00:00 PM") _
And Target.Offset(0, 5).Value < TimeValue("23:00:00 PM") _
Then Target.Offset(0, 7) = "2nd Shift"
If Target.Offset(0, 5).Value >= TimeValue("23:00:00 PM") _
And Target.Offset(0, 5).Value < TimeValue("07:00:00 AM") _
Then Target.Offset(0, 7) = "3rd Shift"
End If
End If
End If
If Target.Value = "" Then
Target.Offset(0, 5).ClearContents
Target.Offset(0, 6).ClearContents
Target.Offset(0, 7).ClearContents
End If
errHandler:
Application.EnableEvents = True
End Sub
================

Thanks,
Brian

 

[Dave Peterson]

This portion of the code:

If Target.Offset(0, 5).Value >= TimeValue("23:00:00 PM") _
And Target.Offset(0, 5).Value < TimeValue("07:00:00 AM") _

is checking to see if a time is later than 11PM and at the same time before than
7AM. There's nothing that can be both (based on just the time--not including
days).

Try changing the AND to OR.

(I didn't look at anything else, though.)

 

[aaron.kempf]

yeah.. you're right

if you're using Excel for _ANYTHING_ then there is an error in your
logic
use a database, jackass

-Aaron

 

[Pete_UK]

You do not need AM and PM in your time strings as you are using 24-hour
clock references. Also, your logic is faulty for the third test - your
value in the cell (0,5) cannot be greater than 23:00:00 AND less than
7:00:00 at the same time. At this stage in your procedure you have
either allocated "1st Shift" or "2nd Shift" to the cell (0,7) or you
haven't allocated a value - you could use this to test for whether it
should be set to "3rd Shift", or alternatively you could change your
condition to:

If Target.Offset(0, 5).Value >= TimeValue("23:00:00") _
Or Target.Offset(0, 5).Value < TimeValue("07:00:00") _
Then Target.Offset(0, 7) = "3rd Shift"

I see that you have got Dave's solution to work, but you might like to
change your original to follow its workings.

Hope this helps.

Pete

 

[robofanuc]

Pete,

Thanks, that is what I was doing wrong; I see that the condition was
never met now. Works like a charm.

Thanks again,
Brian

 

[Pete_UK]

Thanks for the feedback, Brian,

Pete

Pete,

Thanks, that is what I was doing wrong; I see that the condition was
never met now. Works like a charm.

Thanks again,
Brian

 

[aaron.kempf]

yeah you should just use a database; asswhore

Excel shouldn't be used for data entry
Excel shouldn't be used for ANYTHING


-Aaron