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

aroig07

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

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,109,383
Messages
5,528,377
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top