This software uses a GPLv3 Software License, if you are unfamiliar with the terms of this license check the attachments to this file.
Sub Chips_Macros_Weekly_Stats()
‘ Loop through column N and if they hour starts with zero (12 AM) then color it a different color so that it can be changed later
Columns(“N:N”).Select
Set MyRange = Range(“$L$2:$L$1200”)
For Each MyCell In MyRange
If MyCell.Value Like “10:??:??” Then
ElseIf MyCell.Value Like “0:??:??” Then
MyCell.Interior.ColorIndex = 8
‘MyCell.Value = Replace(MyCell, “0”, “24”, , 1)
End If
Next MyCell
‘Generate the column that contains the Entry Hours of every Chip Truck
Range(“N1”).Select
Range(“N1”) = “Entry Hours”
Range(“N2”).Select
Columns(“N:N”).EntireColumn.AutoFit
Range(“N2”).Select
ActiveCell.FormulaR1C1 = “=HOUR(RC[-3])”
Range(“N2”).Select
Selection.AutoFill Destination:=Range(“$N$2:$N$1200”), Type:=xlFillDefault
Range(“N2:N1201”).Select
Range(“N1201”).Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-213
Range(“O988”).Select
ActiveWindow.ScrollRow = 1
Range(“O1”).Select
‘Generate the ranges for the N column
Dim MyRange2 As Range
Dim MyCell2 As Range
‘Loop through the N column and if the value is zero erase it so that it is cleaned up
Set MyRange2 = Range(“$N$2:$N$1200”)
For Each MyCell2 In MyRange2
If MyCell2.Value = “0” Then
MyCell2.Value = “”
End If
Next MyCell2
‘Generate the Daily Hours column that will contain hours 0 through 23 (12 AM through 11 PM)
Range(“P1”) = “Daily Hours”
Range(“P2”).Select
Columns(“P:P”).EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = “0”
Range(“P3”).Select
ActiveCell.FormulaR1C1 = “1”
Range(“P2:P3”).Select
Selection.AutoFill Destination:=Range(“$P$2:$P$25”), Type:=xlFillDefault
Range(“P2:P24”).Select
Range(“R14”).Select
‘Generate Total Chip Trucks by Hour which totals the number of Chip Trucks by the Hour they arrived
Range(“Q1”) = “Weekly Total Chip Trucks by Hour”
Range(“Q2”).Select
Columns(“Q:Q”).EntireColumn.AutoFit
Range(“Q2”).Select
Application.WindowState = xlMinimized
Application.WindowState = xlNormal
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””0″”)”
Range(“Q3”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””1″”)”
Range(“Q4”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””2″”)”
Range(“Q5”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””3″”)”
Range(“Q6”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””4″”)”
Range(“Q7”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””5″”)”
Range(“Q8”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””6″”)”
Range(“Q9”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””7″”)”
Range(“Q10”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””8″”)”
Range(“Q11”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””9″”)”
Range(“Q12”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””10″”)”
Range(“Q13”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””11″”)”
Range(“Q14”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””12″”)”
Range(“Q15”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””13″”)”
Range(“Q16”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””14″”)”
Range(“Q17”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””15″”)”
Range(“Q18”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””16″”)”
Range(“Q19”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””17″”)”
Range(“Q20”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””18″”)”
Range(“Q21”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””19″”)”
Range(“Q22”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””20″”)”
Range(“Q23”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””21″”)”
Range(“Q24”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””22″”)”
Range(“Q25”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],””23″”)”
‘Generate the Total Time the Trucks are here in the M column (for 1200 rows)
Range(“M2”).Select
ActiveCell.FormulaR1C1 = “=RC[-1]-RC[-2]”
Range(“M2”).Select
Selection.AutoFill Destination:=Range(“$M$2:$M$1200”)
Range(“M2:M1200”).Select
ActiveWindow.SmallScroll Down:=774
ActiveWindow.ScrollRow = 1
Columns(“M:M”).Select
Selection.NumberFormat = “h:mm;@”
Range(“M1”).Select
ActiveCell.FormulaR1C1 = “Total Time”
Range(“M2”).Select
Columns(“M:M”).EntireColumn.AutoFit
‘Generate the ranges that will be used to format the M column
Dim MyRange3 As Range
Dim MyCell3 As Range
‘Create the loop that will clean up the M column (if there is a zero there)
Set MyRange3 = Range(“$M$2:$M$1200”)
For Each MyCell3 In MyRange3
If MyCell3.Value = TimeValue(“00:00:00”) Then
MyCell3.Value = “”
End If
Next MyCell3
‘Format the M column so that it will be in the right time format
Range(“M2”).Select
ActiveCell.FormulaR1C1 = “=RC[-1]-RC[-2]”
Range(“M2”).Select
Selection.AutoFill Destination:=Range(“$M$2:$M$1200”)
Range(“M2:M1200”).Select
ActiveWindow.SmallScroll Down:=774
ActiveWindow.ScrollRow = 1
Columns(“M:M”).Select
Selection.NumberFormat = “h:mm;@”
Range(“M1”).Select
ActiveCell.FormulaR1C1 = “Total Time”
Range(“M2”).Select
Columns(“M:M”).EntireColumn.AutoFit
‘Generate the ranges for cleaning the M column
Dim MyRange4 As Range
Dim MyCell4 As Range
‘Generate the loop so that any time’s with the value “00:00:00” are cleaned up and deleted
Set MyRange4 = Range(“$M$2:$M$1200”)
For Each MyCell4 In MyRange4
If MyCell4.Value = TimeValue(“00:00:00”) Then
MyCell4.Value = “”
End If
Next MyCell4
‘Format the Average Time of Weighing Chip Trucks by Hour column
Range(“S1”) = “Weekly Average Time of Weighing Chip Trucks by Hour”
Range(“S2”).Select
Columns(“S:S”).EntireColumn.AutoFit
Range(“S2”).Select
ActiveCell.FormulaR1C1 = “=AVERAGEIF(R2C14:R1200C14, RC[-3], R2C13:R1200C13)”
Range(“S2”).Select
Selection.AutoFill Destination:=Range(“S2:S25”), Type:=xlFillDefault
Range(“S2:S25”).Select
‘Generate the loop for for column s (between 2 and 25). This will clean up and delete all “0:00” values
Set MyRange5 = Range(“S2:S25”)
For Each MyCell5 In MyRange5
If IsError(MyCell5) Then
MyCell5.Value = “0:00”
End If
Next MyCell5
Range(“S26:S36”).Select
Selection.ClearContents
‘Format the times in the s column
Range(“S2:S25”).Select
Selection.NumberFormat = “h:mm;@”
‘Fill the R column with the Daily Average Number of Chip Trucks (it will drag down the column
‘until all the hours are filled in)
Range(“R1”).Select
ActiveCell.FormulaR1C1 = “Daily Average Number of Chip Trucks by Hour”
Range(“R2”).Select
Columns(“R:R”).EntireColumn.AutoFit
Range(“R2”).Select
ActiveCell.FormulaR1C1 = “=AVERAGE(R2C17:R25C17)”
Range(“R2”).Select
Selection.AutoFill Destination:=Range(“$R$2:$R$25”)
Range(“$R$2:$R$25”).Select
Range(“$R$19”).Select
‘In the T column Format the time
Range(“T1”).Select
Range(“T1”) = “Weekly Average Time of Chip Trucks’ by Hour”
Range(“T2”).Select
Columns(“T:T”).EntireColumn.AutoFit
Range(“T2”).Select
Range(“T2”).Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = “=AVERAGEIF(R2C19:R25C19,””<> 0″”)”
Range(“T2”).Select
Selection.AutoFill Destination:=Range(“$T$2:$T$25”)
Range(“T2:T25”).Select
Range(“T2:T25”).Select
Selection.NumberFormat = “h:mm;@”
End Sub