Chip Macros Mon Through Sun (5A)

Chip Macros Mon Through Sun (5A)

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_Mon_Through_Sun()   '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") = "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 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;@"     '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