This is a Side Branch of the Main Branch (Chip Macros Mon Through Sun (7A)). This is testing code so that 24 hours are added to time slots that carry over into the next day (in the K Column or the “Time Weighing Out Column”)
This software uses a GPLv3 Software License, if you are unfamiliar with the terms of this license check the attachments to this file.
Sub Chip_Macros_Mon_Through_Sun_7B()
‘Loop through the K Column and color all cells that start with 0 hour (12 AM)
Columns(“K:K”).Select
Set MyRange = Range(“$K$2:$K$400”)
For Each MyCell In MyRange
If MyCell.Value Like “10:??:??” Then
ElseIf MyCell.Value Like “0:??:??” Then
MyCell.Interior.ColorIndex = 8
End If
Next MyCell
‘Create the Column for Entry Hours and populate it with the Hour that the Truck Arrived
Range(“M1”).Value = “Entry Hours”
Range(“M2”).Select
Columns(“M:M”).EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = “=HOUR(RC[-3])”
Range(“M2”).Select
Selection.AutoFill Destination:=Range(“M2:M401”), Type:=xlFillDefault
Range(“M2:M401”).Select
Range(“M401”).Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-483
‘Generate Ranges for deleting extra zeros
Dim MyRange2 As Range
Dim MyRangeK As Range
Dim MyCell2 As Range
Dim MyCellK As Range
Dim MyStartingCell2 As Range
‘Check the M Column for Zeros that don’t belong and delete them
Set MyRange2 = Range(“$M$2:$M$400”)
Set MyStartingRange2 = Range(“$K$2:$K$400”)
For Each MyCell2 In MyRange2
If MyCell2.Value = “0” Then
MyCell2.Value = “”
End If
Next MyCell2
‘Check the K Column for Zeros that don’t belong and delete them
Set MyRangeK = Range(“$K$2:$K$400”)
For Each MyCellK In MyRangeK
If MyCellK.Value = “0” Then
MyCellK.Value = “”
End If
Next MyCellK
‘This code allows you to check the references in the J column (the time in column) and change the
‘value of the corresponding value (on the same row) of the M column
Dim LoopInteger As Integer
For LoopInteger = 2 To 400
If Range(“J” & LoopInteger).Value Like “0:??:??” Then
Range(“M” & LoopInteger).Value = “0”
End If
Next LoopInteger
‘Create the Daily Hours column and populate it with hours 0 through 24
Range(“O1”).Value = “Daily Hours”
Range(“O2”).Select
Columns(“O:O”).EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = “0”
Range(“O3”).Select
ActiveCell.FormulaR1C1 = “1”
Range(“O2:O3”).Select
Selection.AutoFill Destination:=Range(“O2:O25”), Type:=xlFillDefault
Range(“O2:O24”).Select
‘Generate the Total Chip Trucks by Hour column and account for all hours that are between 0 and 23
Range(“P1”) = “Daily Total Chip Trucks by Hour”
Range(“P2”).Select
Columns(“P:P”).EntireColumn.AutoFit
Range(“P2”).Select
Application.WindowState = xlMinimized
Application.WindowState = xlNormal
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””0″”)”
Range(“P3”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””1″”)”
Range(“P4”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””2″”)”
Range(“P5”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””3″”)”
Range(“P6”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””4″”)”
Range(“P7”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””5″”)”
Range(“P8”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””6″”)”
Range(“P9”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””7″”)”
Range(“P10”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””8″”)”
Range(“P11”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””9″”)”
Range(“P12”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””10″”)”
Range(“P13”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””11″”)”
Range(“P14”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””12″”)”
Range(“P15”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””13″”)”
Range(“P16”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””14″”)”
Range(“P17”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””15″”)”
Range(“P18”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””16″”)”
Range(“P19”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””17″”)”
Range(“P20”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””18″”)”
Range(“P21”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””19″”)”
Range(“P22”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””20″”)”
Range(“P23”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””21″”)”
Range(“P24”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””22″”)”
Range(“P25”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””23″”)”
‘Get column L into a better format for time
Range(“L2”).Select
ActiveCell.FormulaR1C1 = “=RC[-1]-RC[-2]”
Columns(“L:L”).Select
Selection.NumberFormat = “h:mm;@”
Range(“L2”).Select
Selection.AutoFill Destination:=Range(“$L$2:$L$400”)
Range(“L2:L400”).Select
Range(“L1”).Select
Range(“L1”) = “Total Time”
Range(“M4”).Select
Dim MyLCell As Range
Dim MyLRange As Range
‘Delete all the “0:00” in the L Column
‘Set MyLRange = Range(“$L$2:$L$400”)
‘For Each MyLCell In MyLRange
‘If MyLCell.Value = “0:00” Then
‘MyLCell.Value = “”
‘End If
‘Next MyLCell
Dim LoopLColumn As Integer
For LoopLColumn = 2 To 400
If Range(“J” & LoopLColumn).Value Like “” Then
Range(“L” & LoopLColumn).Value = “”
End If
Next LoopLColumn
‘Generating ranges for cleaning the L column
Dim MyRange3 As Range
Dim MyCell3 As Range
‘Looping through 400 rows of the L column in order to make sure that all 00:00:00 are cleaned up and deleted
‘Set MyRange3 = Range(“$L$2:$L$400”)
‘For Each MyCell3 In MyRange3
‘If MyCell3.Value = TimeValue(“0:00”) Then
‘MyCell3.Value = “”
‘End If
‘Next MyCell3
‘Generating the Daily Average Number of Chip Trucks column and it’s averages
Range(“Q1”).Select
Range(“Q1”) = “Daily Average Number of Chip Trucks by Hour”
Range(“Q2”).Select
Columns(“Q:Q”).ColumnWidth = 22
Columns(“Q:Q”).EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = “=AVERAGE(R2C16:R25C16)”
Range(“Q2”).Select
Selection.AutoFill Destination:=Range(“Q2:Q25”)
Range(“Q2:Q25”).Select
‘Generating the Average Time of Weighing Chip Trucks by Hour and the Average time that it takes
Range(“R1”) = “Daily Average Time of Chip Trucks Trips by Hour”
Range(“R2”).Select
Columns(“R:R”).EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = “=AVERAGEIF(C[-5],RC[-3],C[-6])”
Range(“R2”).Select
Selection.AutoFill Destination:=Range(“R2:R25”), Type:=xlFillDefault
Range(“R2:R25”).Select
Selection.NumberFormat = “h:mm;@”
‘Generating the Average Time of Weighing Chip Trucks by Hour and the different averages by hour
Range(“R1”) = “Daily Average Time of Weighing Chip Trucks by Hour”
Range(“R2”).Select
Columns(“R:R”).EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = “=AVERAGEIF(C[-5],RC[-3],C[-6])”
Range(“R2”).Select
Selection.AutoFill Destination:=Range(“R2:R25”), Type:=xlFillDefault
Range(“R2:R25”).Select
Selection.NumberFormat = “h:mm;@”
‘Setting cells that have an error message to the time 0:00
Set MyRange4 = Range(“R2:R25”)
For Each MyCell4 In MyRange
If IsError(MyCell4) Then
MyCell4.Value = “0:00”
End If
Next MyCell4
Range(“$R$2:$R$25”).Font.Name = “Calibri”
Range(“$R$2:$R$25”).Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone
‘Setting cells that have an error message to the time 0:00
Set MyRange5 = Range(“R2:R25”)
For Each MyCell5 In MyRange4
If IsError(MyCell5) Then
MyCell5.Value = “0:00”
End If
Next MyCell5
Range(“$R$2:$R$25”).Font.Name = “Calibri”
Range(“$R$2:$R$25”).Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone
‘Formatting the Average Time of Weighing Chip Trucks by Hour and averaging them only if they are more than zero
Range(“S1”) = “Daily Average Time of Chip Trucks by Hour”
Range(“S2”).Select
Columns(“S:S”).EntireColumn.AutoFit
Range(“S2”).Select
ActiveCell.FormulaR1C1 = “=AVERAGEIF(R2C18:R25C18, “”<> 0″”)”
Range(“S3”).Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll ToRight:=9
Range(“S2”).Select
Selection.AutoFill Destination:=Range(“S2:S25”)
Range(“S2:S25”).Select
Selection.NumberFormat = “h:mm;@”
‘Generate a range of two columns to compare the times of, if the second day is earlier (smaller) than the
‘first day then add 24 hours to it (it is on the following day)
Dim TwoColumns As Range, i As Integer
Set TwoColumns = Selection
With TwoColumns
For i = 2 To 700
Dim ColumnElevenTime
Dim ColumnTenTime
ColumnElevenTimeVariable = Cells(i, 11)
ColumnTenTimeVariable = Cells(i, 10)
If ColumnElevenTimeVariable < ColumnTenTimeVariable And Not ColumnElevenTimeVariable Like “10:??:??” Then
Dim TwoColumnsTime
TwoColumnsTime = TimeValue(Cells(i, 11))
MsgBox (TwoColumnsTime)
End If
Next i
End With
‘Resizing all the columns so that they fit the data
Columns(“L:L”).EntireColumn.AutoFit
Columns(“M:M”).EntireColumn.AutoFit
Columns(“O:O”).EntireColumn.AutoFit
Columns(“P:P”).EntireColumn.AutoFit
Columns(“Q:Q”).EntireColumn.AutoFit
Columns(“R:R”).EntireColumn.AutoFit
Columns(“S:S”).EntireColumn.AutoFit
End Sub