overlapping date ranges

[kalchakra]

Hi,
I am new to the forum! my friends talk a lot about this and glad to be in the forum using Office 2007.

My question---
I work in a manufacturing company which makes different sizes of Nuts on many machines. One machine can do only one size of Nuts for a particular period and can be molded for another variant.I manage an excel sheet defining the date ranges of the machine utility for a particular Nut, this allows us to mark our next point for another size.

The problem is.. after fixing dates for a process on a machine, I many time loose track of the previous marked dates and issue new dates for the same machine that is usually overlapping and lately found out.

Please help me with a solution ~~~ which does not allow overlapping of dates on a particular machine unless the previous dates alloted be changed.

Machines Start date End date
Machine 1 1/25/2011 2/7/2011
Machine 2 1/2/2011 1/25/2011
Machine 3 1/2/2011 1/25/2011
Machine 1 2/19/2011 3/22/2011
Machine 1 1/27/2011 2/11/2011

I use the Table feature of Excel 2007 for this data

 

[jmthompson]

Try this code in your worksheet. Back up your worksheet first. If your file is not laid out exactly like your sample, machine id in a, start date in b and end date in c, you will need to modify the code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("C:C"), Target) Is Nothing Then GoTo newjob:
Dim c As Range
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
On Error GoTo Done2
If Target.Value <> "" Then
    For Each c In Range("A2:A" & lastRow)
        If c = Target.Offset(, -2).Value And c.Offset(, 1) > Target.Offset(, -1).Value And c.Offset(, 1) < Target.Value Then
            GoTo Oops2
        End If
    Next c
End If
Done2:
Exit Sub
Oops2:
    Target.ClearContents
    MsgBox ("This machine has another job schedule to begin before this date")
Exit Sub

newjob:
If Intersect(Range("B:B"), Target) Is Nothing Then Exit Sub
On Error GoTo Done
If Target.Value <> "" Then
    For Each c In Range("A2:A" & lastRow)
        If c = Target.Offset(, -1).Value And c.Offset(, 2) > Target.Value Then
            GoTo Oops
        End If
    Next c
End If
Done:
Exit Sub
Oops:
    Target.ClearContents
    MsgBox ("This machine is not available for this start date")

End Sub

 

[kalchakra]

thank you!

I am a rookie to VBA, please help me with a step by step procedure or with an example attachment.

Thanks in advance.

 

[kalchakra]

thank you!

I am a rookie to VBA, please help me with a step by step procedure or with an example attachment.

Thanks in advance.

Hi Thompson!

can you please help me with an example sheet?? it would be really great

Thanks
kalyan.p@live.com

 

[kalchakra]

Try this code in your worksheet. Back up your worksheet first. If your file is not laid out exactly like your sample, machine id in a, start date in b and end date in c, you will need to modify the code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("C:C"), Target) Is Nothing Then GoTo newjob:
Dim c As Range
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
On Error GoTo Done2
If Target.Value <> "" Then
    For Each c In Range("A2:A" & lastRow)
        If c = Target.Offset(, -2).Value And c.Offset(, 1) > Target.Offset(, -1).Value And c.Offset(, 1) < Target.Value Then
            GoTo Oops2
        End If
    Next c
End If
Done2:
Exit Sub
Oops2:
    Target.ClearContents
    MsgBox ("This machine has another job schedule to begin before this date")
Exit Sub

newjob:
If Intersect(Range("B:B"), Target) Is Nothing Then Exit Sub
On Error GoTo Done
If Target.Value <> "" Then
    For Each c In Range("A2:A" & lastRow)
        If c = Target.Offset(, -1).Value And c.Offset(, 2) > Target.Value Then
            GoTo Oops
        End If
    Next c
End If
Done:
Exit Sub
Oops:
    Target.ClearContents
    MsgBox ("This machine is not available for this start date")

End Sub

Thank you!

I have tried the code, but i am not able to run this "This machine has another job schedule to begin before this date". When the start date & end date is the same and already awarded on the machine, it seems not working

Please help me! I will be grateful.




Mchine Start end
Machine 1 1/25/2011 2/7/2011
Machine 2 1/2/2011 1/25/2011
Machine 3 1/2/2011 1/25/2011
Machine 1 2/19/2011 3/22/2011
Machine 2 1/4/2011 1/24/2011
Machine 1 1/26/2011 1/29/2011
Machine 1 1/29/2011 2/5/2011

I could do this after inserting the code