Chip Macros Mon Through Sun (10A)

Chip Macros Mon Through Sun (10A)

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