Dynamic Calculations with UserForm Input

[aroig07]

Hi All !!

I am having trouble getting a calculation out of my code. I do not know why it is not working the way I wanted to. I have a userform which opens up with checkboxes for two machines that have the same name (Kern 1 and 2) on those machines you could have 1 operator, 2 operators, and it could be out of service (which is what we are using the checkboxes for, to indicate the option for both Kerns. The time I am calculating is dependent on that selection made.

I created a macro which sorts the data in a preferred order (Sort_Calc), after that I am calculating the values for both options whether it be one (changeover1) or two operators (changeover2), the technician is always there and just performs some of the steps parallel to the operators. Each of the changes have a number that is added if the specified variables change to the changeover calculation. I then want to check the selection made in the userform so I can apply the correct time to the job depending if it has one or two operators (if it has one I would add all of the values together, and if its 2 I would get the max value between changeover1 vs changeover2 vs technician.

Here is the code I have up until now, it runs but does not give me the changes and not sure how to go from the calculation to pasting the values in a list on sheet (Daily GANTT):

[COLOR=#0000ff]Private Sub CommandButton1_Click()[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]Sort_Calc[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]Dim pwidth As Long[/COLOR]
[COLOR=#0000ff]Dim metro As Long[/COLOR]
[COLOR=#0000ff]Dim camposition As Long[/COLOR]
[COLOR=#0000ff]Dim inserts As Long[/COLOR]
[COLOR=#0000ff]Dim foldunit As Long[/COLOR]
[COLOR=#0000ff]Dim feeder As Long[/COLOR]
[COLOR=#0000ff]Dim roller As Long[/COLOR]
[COLOR=#0000ff]Dim pocket As Long[/COLOR]
[COLOR=#0000ff]Dim changeover1 As Long 'changeover with one operator[/COLOR]
[COLOR=#0000ff]Dim changeover2 As Long 'changeover with two operators[/COLOR]
[COLOR=#0000ff]Dim machine As String[/COLOR]
[COLOR=#0000ff]Dim DailySchedule As Worksheet[/COLOR]
[COLOR=#0000ff]Dim lastrow As Long[/COLOR]
[COLOR=#0000ff]Dim x As Long[/COLOR]
[COLOR=#0000ff]Dim u As Long[/COLOR]
[COLOR=#0000ff]Dim technician As Long[/COLOR]
[COLOR=#0000ff]Dim finalCO As Long[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]Set DailySchedule = Sheets("Daily Schedule")[/COLOR]
[COLOR=#0000ff]    pwidth = "30"[/COLOR]
[COLOR=#0000ff]    metro = "8"[/COLOR]
[COLOR=#0000ff]    camposition = "10"[/COLOR]
[COLOR=#0000ff]    inserts = "4"[/COLOR]
[COLOR=#0000ff]    foldunit = "4"[/COLOR]
[COLOR=#0000ff]    feeder = "2"[/COLOR]
[COLOR=#0000ff]    roller = "4"[/COLOR]
[COLOR=#0000ff]    pocket = "10"[/COLOR]
[COLOR=#0000ff]    changeover1 = "20"[/COLOR]
[COLOR=#0000ff]    changeover2 = "10"[/COLOR]
[COLOR=#0000ff]    machine = "Kern"[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]'Identify last row of the jobs in the daily schedule[/COLOR]
[COLOR=#0000ff]lastrow = DailySchedule.Cells(Rows.Count, "B").End(xlUp).Row[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]'Check through all of them to see if the variables have changed to calculate changeover time[/COLOR]
[COLOR=#0000ff]        For i = 3 To lastrow[/COLOR]
[COLOR=#0000ff]            u = i - 1[/COLOR]

[COLOR=#0000ff]            If Cells(i, 27).Value = machine Then[/COLOR]

[COLOR=#0000ff]                'first variable - paper width - 30 minutes[/COLOR]
[COLOR=#0000ff]                If Cells(i, 3).Value <> Cells(u, 3).Value Then[/COLOR]
[COLOR=#0000ff]                    changeover1 = changeover1 + pwidth[/COLOR]
[COLOR=#0000ff]                    changeover2 = changeover2 + (pwidth / 2)[/COLOR]
[COLOR=#0000ff]                    technician = technician + 0[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]

[COLOR=#0000ff]                'second variable - camera OMRvs2D - 8 to 12 minutes[/COLOR]
[COLOR=#0000ff]                If Cells(i, 4).Value <> Cells(u, 4).Value Then[/COLOR]
[COLOR=#0000ff]                    If Cells(u, 4).Value = "OMR" And Cells(i, 4).Value = "2D" Then[/COLOR]
[COLOR=#0000ff]                        changeover1 = changeover1 + 0[/COLOR]
[COLOR=#0000ff]                        changeover2 = changeover2 + 0[/COLOR]
[COLOR=#0000ff]                        technician = technician + 8[/COLOR]
[COLOR=#0000ff]                    ElseIf Cells(u, 4).Value = "2D" And Cells(i, 4).Value = "OMR" Then[/COLOR]
[COLOR=#0000ff]                        changeover1 = changeover1 + 0[/COLOR]
[COLOR=#0000ff]                        changeover2 = changeover2 + 0[/COLOR]
[COLOR=#0000ff]                        technician = technician + 12[/COLOR]
[COLOR=#0000ff]                    End If[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]

[COLOR=#0000ff]                'third variable - metro - 8 minutes[/COLOR]
[COLOR=#0000ff]                If Cells(i, 10).Value <> Cells(u, 10).Value Then[/COLOR]
[COLOR=#0000ff]                    changeover1 = changeover1 + metro[/COLOR]
[COLOR=#0000ff]                    changeover2 = changeover2 + (metro / 2)[/COLOR]
[COLOR=#0000ff]                    technician = technician + 0[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]

[COLOR=#0000ff]                'fourth variable - camera position - 10 minutes[/COLOR]
[COLOR=#0000ff]                If Cells(i, 5).Value <> Cells(u, 5).Value Then[/COLOR]
[COLOR=#0000ff]                    changeover1 = changeover1 + 0[/COLOR]
[COLOR=#0000ff]                    changeover2 = changeover2 + 0[/COLOR]
[COLOR=#0000ff]                    technician = technician + camposition[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]

[COLOR=#0000ff]                'fifth variable - inserts - 4 minutes per insert[/COLOR]
[COLOR=#0000ff]                If Cells(i, 7).Value > 0 Then[/COLOR]
[COLOR=#0000ff]                    changeover1 = changeover1 + ((Cells(i, 7).Value) * inserts)[/COLOR]
[COLOR=#0000ff]                    changeover2 = changeover2 + (((Cells(i, 7).Value) * inserts) / 2)[/COLOR]
[COLOR=#0000ff]                    technician = technician + 0[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]

[COLOR=#0000ff]                'sixth variable - folding unit - 4 minutes[/COLOR]
[COLOR=#0000ff]                If Cells(i, 6).Value <> Cells(u, 6).Value Or Cells(i, 8).Value <> Cells(u, 8).Value Or Cells(i, 9).Value <> Cells(u, 9).Value Then[/COLOR]
[COLOR=#0000ff]                    changeover1 = changeover1 + foldunit[/COLOR]
[COLOR=#0000ff]                    changeover2 = changeover2 + (foldunit / 2)[/COLOR]
[COLOR=#0000ff]                    technician = technician + 0[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]

[COLOR=#0000ff]                 'seventh variable - envelope feeder - 2 minutes[/COLOR]
[COLOR=#0000ff]                If Cells(i, 9).Value <> Cells(u, 9).Value Then[/COLOR]
[COLOR=#0000ff]                    changeover1 = changeover1 + feeder[/COLOR]
[COLOR=#0000ff]                    changeover2 = changeover2 + (feeder / 2)[/COLOR]
[COLOR=#0000ff]                    technician = technician + 0[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]

[COLOR=#0000ff]                 'seventh variable - roller - 4 minutes[/COLOR]
[COLOR=#0000ff]                If Cells(i, 6).Value <> Cells(u, 6).Value Then[/COLOR]
[COLOR=#0000ff]                    changeover1 = changeover1 + roller[/COLOR]
[COLOR=#0000ff]                    changeover2 = changeover2 + (roller / 2)[/COLOR]
[COLOR=#0000ff]                    technician = technician + 0[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]

[COLOR=#0000ff]                 'eight variable - pocket - 10 minutes[/COLOR]
[COLOR=#0000ff]                If Cells(i, 6).Value <> Cells(u, 6).Value Then[/COLOR]
[COLOR=#0000ff]                    changeover1 = changeover1 + 0[/COLOR]
[COLOR=#0000ff]                    changeover2 = changeover2 + 0[/COLOR]
[COLOR=#0000ff]                    technician = technician + pocket[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]

[COLOR=#0000ff]            Else[/COLOR]

[COLOR=#0000ff]            End If[/COLOR]

[COLOR=#0000ff]    changeover1 = changeover1[/COLOR]
[COLOR=#0000ff]    changeover2 = changeover2[/COLOR]
[COLOR=#0000ff]    technician = technician[/COLOR]

[COLOR=#0000ff]        Next i[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]'check userform checkboxes to see if there is one (20) or two (10) operators working in the Kern or if its out of service (0)[/COLOR]
[COLOR=#0000ff]        If CheckBox1.Value = True And kern = "Kern 1" Then[/COLOR]
[COLOR=#0000ff]            finalCO = changeover1[/COLOR]
[COLOR=#0000ff]        ElseIf CheckBox2.Value = True And kern = "Kern 1" Then[/COLOR]
[COLOR=#0000ff]            finalCO = changeover2[/COLOR]
[COLOR=#0000ff]        ElseIf CheckBox3.Value = True And kern = "Kern 2" Then[/COLOR]
[COLOR=#0000ff]            finalCO = changeover2[/COLOR]
[COLOR=#0000ff]        ElseIf CheckBox4.Value = True And kern = "Kern 2" Then[/COLOR]
[COLOR=#0000ff]            finalCO = changeover1[/COLOR]
[COLOR=#0000ff]        ElseIf CheckBox5.Value = True Then[/COLOR]
[COLOR=#0000ff]            finalCO = "0"[/COLOR]
[COLOR=#0000ff]        ElseIf CheckBox6.Value = True Then[/COLOR]
[COLOR=#0000ff]            finalCO = "0"[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]

[COLOR=#0000ff]Unload Me[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

THANK YOU SO MUCH !!!!