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" 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 Chip Trucks Trips 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     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