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

aroig07

New Member
Joined
Feb 26, 2019
Messages
42
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):

Code:
[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.
 
Last edited by a moderator:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top