Simon Lloyd
04-26-2009, 06:18 PM
Hi all i have been trying to execute a piece of code if the workboo is opened after midnight but before 6:50am but i can't seem to manage it, any ideas what i'm doing wrong?Dim tm As Date
tm = Now
td = Sheets("Shift 1").Cells.Find(What:=Date, After:=Sheets("Shift 1").Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address
'check for night shift
If tm > TimeSerial(0, 0, 1) Or tm < TimeSerial(6, 50, 0) Then
td = Sheets("Shift 1").Cells.Find(What:=Date, After:=Sheets("Shift 1").Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Address
drng = Range("IV" & Range(td).Row).End(xlToLeft).Address
End If
tm = Now
td = Sheets("Shift 1").Cells.Find(What:=Date, After:=Sheets("Shift 1").Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address
'check for night shift
If tm > TimeSerial(0, 0, 1) Or tm < TimeSerial(6, 50, 0) Then
td = Sheets("Shift 1").Cells.Find(What:=Date, After:=Sheets("Shift 1").Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Address
drng = Range("IV" & Range(td).Row).End(xlToLeft).Address
End If