Calculating between Current and Previous Row based on Variables on Different Worksheets

[aroig07]

Hi All !!!

I am working on a Module that is giving me a headache. I am trying to calculate what I call "changeover time" based on 9 variables contained in a matrix on Sheet("Daily Schedule"). If any of these variables change from the previous selection to the current selection there are times that should be added up. Additionally, I have two machines which name is the same and I have no idea how to separate the jobs between the two. Here is what I have up until now (blue):

[COLOR=#0000ff]Sub CO_Calc()[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#ff0000]'Sort_Calc (still working on this macro to sort the data by the variables and the times be less) [/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]Dim pwidth As Long[/COLOR]
[COLOR=#0000ff]Dim camera As String[/COLOR]
[COLOR=#0000ff]Dim metro As String[/COLOR]
[COLOR=#0000ff]Dim camposition As String[/COLOR]
[COLOR=#0000ff]Dim inserts As String[/COLOR]
[COLOR=#0000ff]Dim foldunit As String[/COLOR]
[COLOR=#0000ff]Dim feeder As String[/COLOR]
[COLOR=#0000ff]Dim roller As String[/COLOR]
[COLOR=#0000ff]Dim pocket As String[/COLOR]
[COLOR=#0000ff]Dim x As Long[/COLOR]
[COLOR=#0000ff]Dim u As Long[/COLOR]
[COLOR=#0000ff]Dim op1 As String[/COLOR]
[COLOR=#0000ff]Dim op2 As String[/COLOR]
[COLOR=#0000ff]Dim technician As String[/COLOR]
[COLOR=#0000ff]Dim changeover1 As String[/COLOR]
[COLOR=#0000ff]Dim changeover2 As String[/COLOR]
[COLOR=#0000ff]Dim machine As String[/COLOR]
[COLOR=#0000ff]Dim lastrow As Long[/COLOR]
[COLOR=#0000ff]Dim COver As Worksheet[/COLOR]
[COLOR=#0000ff]Dim DailySchedule As Worksheet[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]Set pwidth = "30"[/COLOR]
[COLOR=#ff0000]'Set camera = "" working to see because there are two different times to be allocated depending on how it changes[/COLOR]
[COLOR=#0000ff]Set metro = "8"[/COLOR]
[COLOR=#0000ff]Set camposition = "10"[/COLOR]
[COLOR=#ff0000]'Set x = "" working since it is a formula based on the value on the cell of the selection [/COLOR]
[COLOR=#0000ff]Set inserts = x * 4[/COLOR]
[COLOR=#0000ff]Set foldunit = "4"[/COLOR]
[COLOR=#0000ff]Set feeder = "2"[/COLOR]
[COLOR=#0000ff]Set roller = "4"[/COLOR]
[COLOR=#0000ff]Set pocket = "10"[/COLOR]
[COLOR=#0000ff]Set changeover1 = "0"[/COLOR]
[COLOR=#0000ff]Set changeover2 = "0"[/COLOR]
[COLOR=#0000ff]Set machine = "Kern"[/COLOR]
[COLOR=#0000ff]Set COver = Sheets("ChangeOver Times")[/COLOR]
[COLOR=#0000ff]Set DailySchedule = Sheets("Daily Schedule")[/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 Then[/COLOR]
[COLOR=#0000ff]        op1 = "20"[/COLOR]
[COLOR=#0000ff]        op2 = "0"[/COLOR]
[COLOR=#0000ff]        technician = "0"[/COLOR]
[COLOR=#0000ff]        changeover1 = "20"[/COLOR]
[COLOR=#0000ff]    ElseIf CheckBox2.Value = True Then[/COLOR]
[COLOR=#0000ff]        op1 = "10"[/COLOR]
[COLOR=#0000ff]        op2 = "10"[/COLOR]
[COLOR=#0000ff]        technician = "0"[/COLOR]
[COLOR=#0000ff]        changeover1 = "10"[/COLOR]
[COLOR=#0000ff]    ElseIf CheckBox3.Value = True Then[/COLOR]
[COLOR=#0000ff]        op1 = "10"[/COLOR]
[COLOR=#0000ff]        op2 = "10"[/COLOR]
[COLOR=#0000ff]        technician = "0"[/COLOR]
[COLOR=#0000ff]        changeover2 = "10"[/COLOR]
[COLOR=#0000ff]    ElseIf CheckBox4.Value = True Then[/COLOR]
[COLOR=#0000ff]        op1 = "20"[/COLOR]
[COLOR=#0000ff]        op2 = "0"[/COLOR]
[COLOR=#0000ff]        technician = "0"[/COLOR]
[COLOR=#0000ff]        changeover2 = "20"[/COLOR]
[COLOR=#0000ff]    ElseIf CheckBox5.Value = True Then[/COLOR]
[COLOR=#0000ff]        op1 = "0"[/COLOR]
[COLOR=#0000ff]        op2 = "0"[/COLOR]
[COLOR=#0000ff]        technician = "0"[/COLOR]
[COLOR=#0000ff]        changeover2 = "0"[/COLOR]
[COLOR=#0000ff]    ElseIf CheckBox6.Value = True Then[/COLOR]
[COLOR=#0000ff]        op1 = "0"[/COLOR]
[COLOR=#0000ff]        op2 = "0"[/COLOR]
[COLOR=#0000ff]        technician = "0"[/COLOR]
[COLOR=#0000ff]        changeover1 = "0"[/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 = 2 To lastrow[/COLOR]
[COLOR=#0000ff]    u = i + 1[/COLOR]
[COLOR=#0000ff]    If Cells(i, 1).Value <> Cells(u, 1).Value Then[/COLOR]
[COLOR=#0000ff]        If op1.Value > op2.Value Then[/COLOR]
[COLOR=#0000ff]            op1 = op1 + pwidth[/COLOR]
[COLOR=#0000ff]        ElseIf op1.Value < op2.Value Then[/COLOR]
[COLOR=#0000ff]            op2 = op2 + pwidth[/COLOR]
[COLOR=#0000ff]        Else[/COLOR]
[COLOR=#0000ff]            op1 = op1 + pwidth[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]Next i[/COLOR]

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

I have the IF statement for only one of the 9 variables because I started to write and check if its working at the same time and did not want to continue writing IF statements if I was on the wrong path. Also, the standard time if there were no changes in any of the variables on any of the selections, would be 20 minutes (which is the time you see allocated to the checkboxes). Variables are specified by jobs in Sheet("ChangeOver Times"), on columns D through K.