Here is the code that I write for my work on Excel-vba. I use that code to know shifts' and operators' performances.
Main purpose is to divide machines' work to orders and shifts.
Sub Ucshift()
On Error Resume Next
'Cleaning before start
Sheets("Data").Range("A1:R568").AutoFilter
Sheets("Data").Range("A:A,F:I,N:O,S:T").EntireColumn.Hidden = True
Sheets("Dashboard").Range("A2:J1500").ClearContents
Dim shift As Integer
Dim OrderNo As String
Dim unplanned As Long
Dim planned As Long
Dim prodTime As Double
Dim production As Long
Dim ppm_scrap As Long
Dim beginingScrap As Long
Dim ppm As Long
production = 0
prodTime = 0
unplanned = 0
planned = 0
ppm = 0
ppm_scrap = 0
beginingScrap = 0
ppm = 0
Dim sayim As Integer
Dim j As Long
Dim machine As String
Dim i As Long
Dim sorgu() As String
ReDim sorgu(Sheets("Dashboard").Range("K12222").End(3).Row - 1)
Dim temp As Integer
'will loop each machine
For j = 2 To Sheets("Dashboard").Range("K12222").End(3).Row
machine = Sheets("Dashboard").Range("K" & j)
'Keeps how many rows that machine has, to know when it ends
sorgu(j - 1) = (WorksheetFunction.VLookup(machine, Sheets("Data").Range("J:J"), 1, 0))
If (sorgu(j - 1) = "") Then
GoTo Ending 'To pass the next machine
End If
sayim = 0
'Take that machines row to another sheet to work on them easily
With Sheets("Data")
.Range("$A$1:$T$5000").AutoFilter Field:=10, _
Operator:=xlFilterValues
.Range("$A$1:$T$5000").AutoFilter Field:=10, Criteria1:=machine
.AutoFilter.Sort. _
SortFields.Clear
.AutoFilter.Sort. _
SortFields.Add Key:=Range("D1:D5000"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With .AutoFilter. _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Model").Range("A1:R700").Clear
.Range("B1:R" & .Range("B500000").End(xlUp).Row).Copy
End With
With Sheets("Model")
.Range("A1").PasteSpecial
'Take the order no.
OrderNo = .Range("D2").Value
'Go through machine rows
For i = 2 To .Range("A50000").End(xlUp).Row + 1
If .Cells(i, 4) = OrderNo Then
'At first loop, we dont know the shift for sure.
If i <> 2 Then
temp = shift
End If
'Decide the shift
If (Format(.Range("G" & i), "hh:mm") >= "07:30" And Format(.Range("G" & i), "hh:mm") <= "16:59") Then 'And (Format(.Range("H" & i), "hh:mm") > "07:30" And Format(.Range("H" & i), "hh:mm") <= "17:00")
shift = 1
ElseIf (Format(.Range("G" & i), "hh:mm") >= "17:00" Or Format(.Range("G" & i), "hh:mm") < "00:30") Then ' And (Format(.Range("H" & i), "hh:mm") <= "00:31" Or Format(.Range("H" & i), "hh:mm") > "17:01"))
shift = 2
Else
shift = 3
End If
'At first loop, temp is loaded with first shift after idenfying the shift. Temp is used to know when shift changes
If i = 2 Then
temp = shift
End If
If temp = shift Then 'If shift didnt change, continue
sayim = 1
'Counting unplanned, planned stops and production time
If .Cells(i, 6).Value = "D01" Or .Cells(i, 6).Value = "D02" Or .Cells(i, 6).Value = "D03" Or .Cells(i, 6).Value = "D04" Or .Cells(i, 6).Value = "D05" Or .Cells(i, 6).Value = "D06" Or .Cells(i, 6).Value = "D07" Or .Cells(i, 6).Value = "D08" Or .Cells(i, 6).Value = "D09" Or .Cells(i, 6).Value = "D12" Or .Cells(i, 6).Value = "D13" Or .Cells(i, 6).Value = "D14" Or .Cells(i, 6).Value = "D15" Or .Cells(i, 6).Value = "D16" Or .Cells(i, 6).Value = "D17" Or .Cells(i, 6).Value = "D18" Or .Cells(i, 6).Value = "D19" Or .Cells(i, 6).Value = "D20" Or .Cells(i, 6).Value = "D21" Or .Cells(i, 6).Value = "D22" Or .Cells(i, 6).Value = "D23" Or .Cells(i, 6).Value = "D24" Or .Cells(i, 6).Value = "D25" Or .Cells(i, 6).Value = "D26" Or .Cells(i, 6).Value = "D27" Or .Cells(i, 6).Value = "D28" Or .Cells(i, 6).Value = "D29" Or .Cells(i, 6).Value = "D30" Then
unplanned = unplanned + (.Cells(i, 8) - .Cells(i, 7)) * 1440
ElseIf .Cells(i, 6).Value = "D00" Or .Cells(i, 6).Value = "D10" Or .Cells(i, 6).Value = "D11" Then
planned = planned + (.Cells(i, 8) - .Cells(i, 7)) * 1440
ElseIf .Cells(i, 6).Value = 0 Then
prodTime = prodTime + (.Cells(i, 8) - .Cells(i, 7)) * 24
End If
'getting scrap amounts
If .Cells(i, 11).Value = "F0A" Or .Cells(i, 11).Value = "F0B" Then
beginingScrap = beginingScrap + .Cells(i, 10)
ElseIf .Cells(i, 11).Value <> "" Then
ppm_scrap = ppm_scrap + .Cells(i, 10)
End If
'Getting production amount
production = production + .Cells(i, 9)
Else 'If shift has changed
'Write the results of the shift to Dashboard
If sayim <> 0 Then
Sheets("Dashboard").Range("A" & Sheets("Dashboard").Range("A10000").End(xlUp).Row + 1) = temp
Sheets("Dashboard").Range("B" & Sheets("Dashboard").Range("b10000").End(xlUp).Row + 1) = machine
Sheets("Dashboard").Range("c" & Sheets("Dashboard").Range("c10000").End(xlUp).Row + 1) = OrderNo
Sheets("Dashboard").Range("d" & Sheets("Dashboard").Range("d10000").End(xlUp).Row + 1) = unplanned
Sheets("Dashboard").Range("e" & Sheets("Dashboard").Range("e10000").End(xlUp).Row + 1) = planned
Sheets("Dashboard").Range("g" & Sheets("Dashboard").Range("g10000").End(xlUp).Row + 1) = production
Sheets("Dashboard").Range("h" & Sheets("Dashboard").Range("h10000").End(xlUp).Row + 1) = beginingScrap
Sheets("Dashboard").Range("I" & Sheets("Dashboard").Range("I10000").End(xlUp).Row + 1) = ppm_scrap
ppm = (ppm_scrap / production) * 1000000
Sheets("Dashboard").Range("j" & Sheets("Dashboard").Range("j10000").End(xlUp).Row + 1) = ppm
Sheets("Dashboard").Range("f" & Sheets("Dashboard").Range("f10000").End(xlUp).Row + 1) = prodTime
sayim = 0
End If
'set counters to zero
beginingScrap = 0
ppm_scrap = 0
ppm = 0
production = 0
prodTime = 0
planned = 0
unplanned = 0
'Start counting for the new shift
If .Cells(i, 6).Value = "D01" Or .Cells(i, 6).Value = "D02" Or .Cells(i, 6).Value = "D03" Or .Cells(i, 6).Value = "D04" Or .Cells(i, 6).Value = "D05" Or .Cells(i, 6).Value = "D06" Or .Cells(i, 6).Value = "D07" Or .Cells(i, 6).Value = "D08" Or .Cells(i, 6).Value = "D09" Or .Cells(i, 6).Value = "D12" Or .Cells(i, 6).Value = "D13" Or .Cells(i, 6).Value = "D14" Or .Cells(i, 6).Value = "D15" Or .Cells(i, 6).Value = "D16" Or .Cells(i, 6).Value = "D17" Or .Cells(i, 6).Value = "D18" Or .Cells(i, 6).Value = "D19" Or .Cells(i, 6).Value = "D20" Or .Cells(i, 6).Value = "D21" Or .Cells(i, 6).Value = "D22" Or .Cells(i, 6).Value = "D23" Or .Cells(i, 6).Value = "D30" Then
unplanned = unplanned + (.Cells(i, 8) - .Cells(i, 7)) * 1440
ElseIf .Cells(i, 6).Value = "D00" Or .Cells(i, 6).Value = "D10" Or .Cells(i, 6).Value = "D11" Then
planned = planned + (.Cells(i, 8) - .Cells(i, 7)) * 1440
ElseIf .Cells(i, 6).Value = 0 Then
prodTime = prodTime + (.Cells(i, 8) - .Cells(i, 7)) * 24
End If
If .Cells(i, 11).Value = "F0A" Or .Cells(i, 11).Value = "F0B" Then
beginingScrap = beginingScrap + .Cells(i, 10)
Else
ppm_scrap = ppm_scrap + .Cells(i, 10)
End If
production = production + .Cells(i, 9)
End If
Else 'If order no has changed
'Write last order no's last shift's results
Sheets("Dashboard").Range("A" & Sheets("Dashboard").Range("A10000").End(xlUp).Row + 1) = shift
Sheets("Dashboard").Range("B" & Sheets("Dashboard").Range("b10000").End(xlUp).Row + 1) = machine
Sheets("Dashboard").Range("c" & Sheets("Dashboard").Range("c10000").End(xlUp).Row + 1) = OrderNo
Sheets("Dashboard").Range("d" & Sheets("Dashboard").Range("d10000").End(xlUp).Row + 1) = unplanned
Sheets("Dashboard").Range("e" & Sheets("Dashboard").Range("e10000").End(xlUp).Row + 1) = planned
Sheets("Dashboard").Range("g" & Sheets("Dashboard").Range("g10000").End(xlUp).Row + 1) = production
Sheets("Dashboard").Range("h" & Sheets("Dashboard").Range("h10000").End(xlUp).Row + 1) = beginingScrap
Sheets("Dashboard").Range("I" & Sheets("Dashboard").Range("I10000").End(xlUp).Row + 1) = ppm_scrap
ppm = (ppm_scrap / production) * 1000000
Sheets("Dashboard").Range("j" & Sheets("Dashboard").Range("j10000").End(xlUp).Row + 1) = ppm
Sheets("Dashboard").Range("f" & Sheets("Dashboard").Range("f10000").End(xlUp).Row + 1) = prodTime
beginingScrap = 0
ppm_scrap = 0
ppm = 0
prodTime = 0
production = 0
planned = 0
unplanned = 0
OrderNo = .Cells(i, 4).Value 'change order no to new one
'count for the new order no and shift
If .Cells(i, 6).Value = "D01" Or .Cells(i, 6).Value = "D02" Or .Cells(i, 6).Value = "D03" Or .Cells(i, 6).Value = "D04" Or .Cells(i, 6).Value = "D05" Or .Cells(i, 6).Value = "D06" Or .Cells(i, 6).Value = "D07" Or .Cells(i, 6).Value = "D08" Or .Cells(i, 6).Value = "D09" Or .Cells(i, 6).Value = "D12" Or .Cells(i, 6).Value = "D13" Or .Cells(i, 6).Value = "D14" Or .Cells(i, 6).Value = "D15" Or .Cells(i, 6).Value = "D16" Or .Cells(i, 6).Value = "D17" Or .Cells(i, 6).Value = "D18" Or .Cells(i, 6).Value = "D19" Or .Cells(i, 6).Value = "D20" Or .Cells(i, 6).Value = "D21" Or .Cells(i, 6).Value = "D22" Or .Cells(i, 6).Value = "D23" Or .Cells(i, 6).Value = "D30" Then
unplanned = unplanned + (.Cells(i, 8) - .Cells(i, 7)) * 1440
ElseIf .Cells(i, 6).Value = "D00" Or .Cells(i, 6).Value = "D10" Or .Cells(i, 6).Value = "D11" Then
planned = planned + (.Cells(i, 8) - .Cells(i, 7)) * 1440
ElseIf .Cells(i, 6).Value = 0 Then
prodTime = prodTime + (.Cells(i, 8) - .Cells(i, 7)) * 24
End If
If .Cells(i, 11).Value = "F0A" Or .Cells(i, 11).Value = "F0B" Then
beginingScrap = beginingScrap + .Cells(i, 10)
Else
ppm_scrap = ppm_scrap + .Cells(i, 10)
End If
production = production + .Cells(i, 9)
End If
Next
End With
Ending:
Next
End Sub
I write the comments for almost all codes for you to understand. I am not a software engineer you will notice quickly.
I just want to learn if there is way to do the same task with easier, more readable and flexible codes. What I mean with flexible is, easily change the code when number of shifts has changed or etc.
You can find the Excel here
https://www.dropbox.com/s/uaizjqph7ltfx58/Analiz%20v1.3.xlsm?dl=0