Sub pupdate()
Application.ScreenUpdating = False
Sheet4.Select 'count of items in query
Dim a As Integer
a = Application.CountA(Sheet4.Range(Cells(2, 5), Cells(1000000, 5)))
Application.ScreenUpdating = True
'prompt for first item setup
UserForm2.Label1.Caption = "Please enter the planned start date and time for the current order:" & vbNewLine & Sheet4.Cells(2, 5).Value
UserForm2.Show
Sheet1.Cells(6, 6).Value = UserForm2.Label6.Caption
Sheet1.Cells(6, 7).Value = UserForm2.Label7.Caption
'UserForm1.Label1.Caption = "Was or is the Set-up of " & Sheet4.Cells(2, 5).Value & " performed outside of scheduled production time?"
'UserForm1.Show
Application.ScreenUpdating = False
'Dim var000
'var000 = Application.VLookup(Sheet3.Cells(2 + j, 1).Value, Sheet2.Range("E6:F1000000"), 2, False)
'Dim iserr000 As Boolean
'iserr000 = IsError(var000)
'If iserr000 = True Then
'Sheet3.Cells(2 + j, 3).Value = "???"
'Else
'Sheet3.Cells(2 + j, 3).Value = Application.VLookup(Sheet3.Cells(2 + j, 1).Value, Sheet2.Range("E6:F1000000"), 2, False)
'End If
For b = 1 To a 'loop for each line item in plan, get the basics from the query
Sheet1.Cells(5 + b, 2).Value = Sheet4.Cells(1 + b, 5).Value 'add the part number to the plan
Sheet1.Cells(5 + b, 3).Value = Sheet4.Cells(1 + b, 11).Value 'add star rating to the plan
Sheet1.Cells(5 + b, 4).Value = Sheet4.Cells(1 + b, 6).Value 'add the quantity to the plan
Sheet1.Cells(5 + b, 14).Value = Sheet4.Cells(1 + b, 12).Value 'check if setup is performed outside scheduled production time
If IsEmpty(Sheet1.Cells(5 + b, 14).Value) = True Then
Else
Sheet1.Cells(5 + b, 13).Value = 0 'force override setup time to 0
End If
Sheet1.Cells(5 + b, 5).Value = Sheet4.Cells(1 + b, 13).Value 'check if setup is performed outside scheduled production time
Dim var000 'add cycle time to the plan
var000 = Application.VLookup(Sheet4.Cells(1 + b, 5).Value, Sheet5.Range("A2:C1000000"), 2, False) 'check if there is a cycle time in the table for this part.
Dim iserr000 As Boolean
iserr000 = IsError(var000)
If iserr000 = True Then 'when there is an error, this should only occur when the part number is not in the list.
Application.ScreenUpdating = True
UserForm3.Label1.Caption = "We don't have a Cycle time or Setup time for this part,:" & vbNewLine & Sheet4.Cells(1 + b, 5).Value & vbNewLine & "What would you like me to put down?"
UserForm3.Show
Application.ScreenUpdating = False
Sheet5.Select 'count of items in Cycle Time Table
Dim ac As Integer
ac = Application.CountA(Sheet5.Range(Cells(2, 1), Cells(1000000, 1)))
Sheet5.Cells(2 + ac, 1).Value = Sheet1.Cells(5 + b, 2).Value 'add the part to the table
Sheet5.Cells(2 + ac, 2).Value = UserForm3.Label3.Caption 'add the manually entered cycle time to the table
Sheet1.Cells(5 + b, 10).Value = UserForm3.Label3.Caption 'add the cycle time to the plan
Sheet5.Cells(2 + ac, 3).Value = UserForm3.Label5.Caption 'add the setup time to the table
Else
Sheet1.Cells(5 + b, 10).Value = Application.VLookup(Sheet4.Cells(1 + b, 5).Value, Sheet5.Range("A2:C1000000"), 2, False)
End If
Sheet1.Cells(5 + b, 12).Value = Application.VLookup(Sheet4.Cells(1 + b, 5).Value, Sheet5.Range("A2:C1000000"), 3, False) 'add setup time to the plan.
Sheet1.Cells(5 + b, 18).Value = Sheet4.Cells(1 + b, 14).Value 'add the quantity to the plan
Next b
'we want all the basics already in the dashboard before these calculations. so perform a second loop to get the more difficult calculations and comparisons.
justcalcs
Sheet1.Select
Sheet1.Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub
Sub justcalcs()
Application.ScreenUpdating = False
Sheet1.Select
Sheet1.Cells(1, 2).Value = 1
Dim d As Integer 'count of items in the dashboard
d = Application.CountA(Sheet1.Range(Cells(6, 2), Cells(1000000, 2)))
'Grab current time, day etc
Dim w
Dim x(1 To 6)
Dim y, z
w = Now
x(1) = Year(w)
x(2) = Month(w)
x(3) = Day(w)
x(4) = Hour(w)
x(5) = Minute(w)
x(6) = Second(w)
y = DateValue(w)
z = TimeValue(w)
If Sheet1.Cells(6, 6).Value = Sheet2.Cells(12, 1).Value Then
Else
Do Until Sheet1.Cells(6, 6).Value = Sheet2.Cells(12, 1).Value 'clear out old scheduled days
Sheet2.Select
Sheet2.Rows(3).EntireRow.Delete
Loop
End If
'loop to perform time calculations and logical decisions
For e = 1 To d
'total run time calculation
If Sheet1.Cells(5 + e, 14).Value = "Yes" Then 'if yes then disregard setup time info
If IsEmpty(Sheet1.Cells(5 + e, 11).Value) Then 'if there is an override cycle time then use that
Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 10).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 16).Value
Else
Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 11).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 16).Value
End If
Else
If IsEmpty(Sheet1.Cells(5 + e, 13).Value) Then 'if there is an override setup time, use that
If IsEmpty(Sheet1.Cells(5 + e, 11).Value) Then 'if there is an override cycle time then use that
Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 10).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 12).Value + Sheet1.Cells(5 + e, 16).Value
Else
Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 11).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 12).Value + Sheet1.Cells(5 + e, 16).Value
End If
Else 'otherwise just use the lookup setup time
If IsEmpty(Sheet1.Cells(5 + e, 11).Value) Then 'if there is an override cycle time then use that
Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 10).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 13).Value + Sheet1.Cells(5 + e, 16).Value
Else
Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 11).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 13).Value + Sheet1.Cells(5 + e, 16).Value
End If
End If
End If
'calculate end day and end time
Sheet1.Cells(4, 1).Value = TimeValue(TimeSerial(Int(Sheet1.Cells(5 + e, 17).Value / 60), Sheet1.Cells(5 + e, 17).Value - (Int(Sheet1.Cells(5 + e, 17).Value / 60) * 60), 0)) 'run time to be produced
If IsEmpty(Sheet1.Cells(5 + e, 15).Value) = False Then 'then there is a yes there, so we just make the end time say by end of shift and use the same day
Sheet1.Cells(5 + e, 8).Value = Sheet1.Cells(5 + e, 6).Value
Sheet1.Cells(5 + e, 9).Value = "Today"
Else
'if (shift planned time - passed time) >= (time needed for order) then we can just fit it in the remaining shift. so just use the start date as the end date.
If (TimeValue(TimeSerial((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2)), (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3)), 0)) _
- (Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value)) _
> TimeValue(TimeSerial(Int(Sheet1.Cells(5 + e, 17).Value / 60), Sheet1.Cells(5 + e, 17).Value - (Int(Sheet1.Cells(6, 17).Value / 60) * 60), 0)) _
Or (TimeValue(TimeSerial((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2)), (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3)), 0)) _
- (Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value)) = TimeValue(TimeSerial(Int(Sheet1.Cells(5 + e, 17).Value / 60), Sheet1.Cells(5 + e, 17).Value - (Int(Sheet1.Cells(6, 17).Value / 60) * 60), 0)) Then
Sheet1.Cells(5 + e, 9).Value = TimeValue(TimeSerial(Hour(Sheet1.Cells(5 + e, 7).Value), Minute(Sheet1.Cells(5 + e, 7).Value), 0)) _
+ TimeValue(TimeSerial(Int(Sheet1.Cells(7, 17).Value / 60), Sheet1.Cells(7, 17).Value - (Int(Sheet1.Cells(7, 17).Value / 60) * 60), 0)) ' calculate the end time.
Sheet1.Cells(5 + e, 8).Value = Sheet1.Cells(5 + e, 6).Value 'copy the date from the start date
Sheet1.Cells(6 + e, 6).Value = Sheet1.Cells(5 + e, 8).Value 'copy date to the next line item's start time
Sheet1.Cells(6 + e, 7).Value = Sheet1.Cells(5 + e, 9).Value 'copy end time to next item start time
'not adding a day to the day counter so that the next e will get the same start date
Sheet1.Cells(4, 1).Value = -0.0001 'this forces the counter to be less than the threshold even if it is 0
Else
'if (shift planned time + threshold - passed time) >= (time needed for order) we will add the end time accurately but make the start day and time for the next order begin next shift as well as cause the setup to take place off shift
If TimeValue(TimeSerial(Int(( _
(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60)) / 60), ((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60)) _
- (Int(((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60)) / 60) * 60), 0)) - _
(Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value) _
> TimeValue(TimeSerial(Int(Sheet1.Cells(6, 17).Value / 60), Sheet1.Cells(6, 17).Value - (Int(Sheet1.Cells(6, 17).Value / 60) * 60), 0)) Or _
TimeValue(TimeSerial(Int(( _
(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60)) / 60), ((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60)) _
- (Int(((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60)) / 60) * 60), 0)) - _
(Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value) _
= TimeValue(TimeSerial(Int(Sheet1.Cells(6, 17).Value / 60), Sheet1.Cells(6, 17).Value - (Int(Sheet1.Cells(6, 17).Value / 60) * 60), 0)) Then
Sheet1.Cells(5 + e, 9).Value = TimeValue(TimeSerial(Hour(Sheet1.Cells(5 + e, 7).Value), Minute(Sheet1.Cells(5 + e, 7).Value), 0)) _
+ TimeValue(TimeSerial(Int(Sheet1.Cells(5 + e, 17).Value / 60), Sheet1.Cells(5 + e, 17).Value - (Int(Sheet1.Cells(5 + e, 17).Value / 60) * 60), 0)) _
+ (Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value) ' calculate the end time.
Sheet1.Cells(5 + e, 8).Value = Sheet1.Cells(5 + e, 6).Value 'copy the date from the start date
'check if next date has scheduled time, if not then goes to the first date with scheduled time (updates the days number)
If IsEmpty(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 4).Value) = True Then
Do Until IsEmpty(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 4).Value) = False
Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + 1
Loop
Else
End If
Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + 1
Sheet1.Cells(6 + e, 6).Value = DateValue(DateSerial(Year(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 1).Value), Month(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 1).Value), Day(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 1).Value))) 'get the start day off of the schedules page according to how many additional shifts forward we must go
Sheet1.Cells(6 + e, 7).Value = Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value 'use shift start time for planned start time of the next line item
Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + 1 'add one to the day counter since we used up the entire shift
Sheet1.Cells(4, 1).Value = -0.0001 'this forces the counter to be less than the threshold even if it is 0
Else 'this is if the order must roll onto another shift
'create Machine Time Counter for loop
Sheet1.Cells(2, 2).Value = TimeValue(TimeSerial( _
Int(( _
(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60) _
) / 60), _
((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60)) _
- (Int(( _
(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60) _
) / 60) * 60) _
, 0))
'create additional day counter
Sheet1.Cells(3, 2).Value = 1
Do Until Sheet1.Cells(4, 1).Value < Sheet1.Cells(2, 2).Value Or Sheet1.Cells(4, 1).Value = Sheet1.Cells(2, 2).Value
'add the next shift and threshold to the Mach time holder
Sheet1.Cells(2, 2).Value = Sheet1.Cells(2, 2).Value + _
TimeValue(TimeSerial( _
Int(( _
(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60) _
) / 60), _
((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60)) _
- (Int(( _
(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 2).Value * 60) _
+ Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 3).Value _
+ Int(Sheet1.Cells(3, 18).Value * 60) _
) / 60) * 60) _
, 0))
'add 1 to the additional day counter
Sheet1.Cells(3, 2).Value = Sheet1.Cells(3, 2).Value + 1
Loop
'now actually calculate the end day and time
Sheet1.Cells(5 + e, 8).Value = Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 1).Value 'the day is easy
'add the additional day counter into the day counter
Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value
Sheet1.Cells(3, 2).Select ' clear out additional day counter
Selection.ClearContents
'calculate end time
Sheet1.Cells(5 + e, 9).Value = (TimeValue(TimeSerial(Hour(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 5)), Minute(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 5)), 0))) _
- ((Sheet1.Cells(2, 2).Value) - (Sheet1.Cells(4, 1).Value))
'check if end time is during threshold, if it is then next item start date and time is beginning of next shift
If ((Sheet1.Cells(2, 2).Value) - (Sheet1.Cells(4, 1).Value)) > TimeValue(TimeSerial(Int(Sheet1.Cells(3, 18).Value), ((Sheet1.Cells(3, 18).Value * 60) - (Int(Sheet1.Cells(3, 18).Value) * 60)), 0)) Then
Sheet1.Cells(6 + e, 6).Value = Sheet1.Cells(5 + e, 8).Value 'just pull it off the last items end day
Sheet1.Cells(6 + e, 7).Value = Sheet1.Cells(5 + e, 9).Value 'same critter, just get it from the last items end time
Else
'because the last item ended during previous shift's threshold time, we need to check if this next item is supposed to run off shift, if it is then we want add it in the same day
If IsEmpty(Sheet1.Cells(6 + e, 14).Value) = True Then 'when yes we only want to perform the setup on the second shift so we simply change the run time to reflect and force the overide to 0
'because there is no yes here we want to assume that setup will take place on second shift so we change the total run time to reflect no setup, add a yes, and add a 0 for setup time
Sheet1.Cells(6 + e, 13).Value = 0 'no setup time Yay!
Sheet1.Cells(6 + e, 14).Value = "Yes"
If IsEmpty(Sheet1.Cells(5 + e, 11).Value) Then 'recalculate the total run time, if there is an override cycle time then use that
Sheet1.Cells(6 + e, 17).Value = Round(((Sheet1.Cells(6 + e, 4).Value * Sheet1.Cells(6 + e, 10).Value) / 60) + 0.5, 0) + Sheet1.Cells(6 + e, 16).Value
Else
Sheet1.Cells(6 + e, 17).Value = Round(((Sheet1.Cells(6 + e, 4).Value * Sheet1.Cells(6 + e, 11).Value) / 60) + 0.5, 0) + Sheet1.Cells(6 + e, 16).Value
End If
Else 'we were testing for a yes, there was one, so we know that the total run time is correct and does not need an update. but we stillneed to add the start time for the next item as the next shifts start date and time
Do Until IsEmpty(Sheet1.Cells(1, 3).Value) = False
'create additional day counter AGAIN
Sheet1.Cells(3, 2).Value = 1
If IsEmpty(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 4).Value) = True Then 'if there is not start time for the shift we know to go to the next one.
Sheet1.Cells(3, 2).Value = Sheet1.Cells(3, 2).Value + 1
Else 'always add one to the day counter when we have found a shift with time
Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value + 1
Sheet1.Cells(1, 3).Value = 1
End If
Loop
Sheet1.Cells(1, 3).Select
Selection.ClearContents
Sheet1.Cells(3, 2).Select
Selection.ClearContents
End If
'no need to check if it is supposed to run off shift, because that is the first thing we tested in this loop.. so just assume that it will get taken care of in the next e
End If
End If
End If
Sheet1.Select
Sheet1.Cells(2, 2).Select
Selection.ClearContents
End If
Next e
Sheet1.Cells(3, 1).Value = Now()
Sheet1.Cells(4, 1).Select
Selection.ClearContents
Sheet1.Cells(1, 2).Select
Selection.ClearContents
Sheet1.Cells(3, 2).Select
Selection.ClearContents
Application.ScreenUpdating = True
End Sub
