This is a Side Branch of the Main Branch (Chip Macros Mon Through Sun (7A)). This is testing code so that entries that start between 12 AM and 1 AM (Hour Zero) are able to be integrated into Current Code.
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 MyCell2 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
‘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
‘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(“00:00: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;@”
‘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