| You are currently viewing a revision titled "Logistic Macros Mon Through Sun (4A)", saved on July 6, 2022 at 11:52 am by Patrick Burcham | |
|---|---|
| Title | Logistic Macros Mon Through Sun (4A) |
| Content | This software uses a GPLv3 Software License, if you are unfamiliar with the terms of this license check the attachments to this file.
'Start the Program Sub Logistics_Macros_Mon_Through_Sun() 'Generate the range that contains two columns and then compare them to see if the second column is larger than the first 'If the Second column is smaller than the first column then add 24 hours to it (it is on another day than the original time) Dim TwoColumns As Range, i As Integer Set TwoColumns = Selection With TwoColumns For i = 2 To 200 If Cells(i, 4) < Cells(i, 3) Then 'MsgBox ("Column D in" & Cells(i, 4) & "is smaller") Cells(i, 4).Value = Cells(i, 4) + 2400 End If Next i End With 'Loop through all rows of column C and correct the time format of every cell in the column Columns("C:C").Select Set MyRange = Range("$C$2:$C$200") For Each MyCell In MyRange If MyCell.Value Like "???" Then Dim Three_Characters Three_Characters = MyCell.Value MyCell.Value = Left(Three_Characters, 1) & ":" & Right(Three_Characters, 2) & ":00" ElseIf MyCell.Value Like "????" Then Dim Four_Characters Four_Characters = MyCell.Value MyCell.Value = Left(Four_Characters, 2) & ":" & Right(Four_Characters, 2) & ":00" End If Next MyCell Columns("C:C").EntireColumn.AutoFit 'Select Collumn D and correct the time format of every cell in the column Columns("D:D").Select Set MyRange = Range("$D$2:$D$200") For Each MyCellD In MyRange If MyCellD.Value Like "???" Then Dim Three_CharactersD Three_CharactersD = MyCellD.Value MyCellD.Value = Left(Three_CharactersD, 1) & ":" & Right(Three_CharactersD, 2) & ":00" ElseIf MyCellD.Value Like "????" Then Dim Four_CharactersD Four_CharactersD = MyCellD.Value MyCellD.Value = Left(Four_CharactersD, 2) & ":" & Right(Four_CharactersD, 2) & ":00" End If Next MyCellD 'Generate the Time Difference column, subtract the second time column from the first time column to get the time to unload ActiveCell.FormulaR1C1 = "Time Difference" Range("M2").Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With ActiveCell.FormulaR1C1 = "=RC[-9]-RC[-10]" Range("M2").Select Selection.AutoFill Destination:=Range("M2:M500") Range("M2:M380").Select Range("O16").Select 'Generate ranges and use them to delete all unnecessary zeros in the M column Dim MyRangeDeleteZero As Range Dim MyCellDeleteZero As Range Set MyRangeDeleteZero = Range("$M$2:$M$200") For Each MyCellDeleteZero In MyRange If MyCellDeleteZero.Value = "00:00:00" Then MyCellDeleteZero.Value = "" End If Next MyCellDeleteZero 'Generate ranges and use them to delete all unnecessary zeros in the C column Dim TwoColumnsZero As Range, T As Integer Set TwoColumnsZero = Selection With TwoColumnsZero For T = 2 To 200 If IsEmpty(Cells(T, 3).Value) Then Cells(T, 13).Value = "" End If Next T End With 'Format the Time Difference column so that data fits the column ActiveWindow.SmallScroll Down:=-228 Range("M1").Select ActiveWindow.SmallScroll Down:=-63 ActiveCell.FormulaR1C1 = "Time Difference" Range("M2").Select Columns("M:M").EntireColumn.AutoFit 'Generate the Entry Hours column and format it to fit the data Range("N1").Value = "Entry Hours" Range("N2").Select Columns("N:N").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=HOUR(RC[-11])" Range("N2").Select Selection.AutoFill Destination:=Range("N2:N200"), Type:=xlFillDefault Range("N2:N200").Select Range("N201").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=-483 'Generate a range and use it to delete unneccary zeros in the N column Dim TwoColumnsN As Range, i2 As Integer Set TwoColumnsN = Selection With TwoColumnsN For i2 = 2 To 200 If Cells(i2, 3) = "" Then Cells(i2, 14).Value = "" End If Next i2 End With 'Format the Daily Hours column to fit the data Range("P1").Value = "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("P2:P25"), Type:=xlFillDefault Range("P2:P24").Select 'Generate the Total Trucks by Hour column and record the number of logistic trucks by the hour they arrive Range("Q1").Select ActiveCell.FormulaR1C1 = "Daily Total Logistic Trucks by Hour" ActiveCell.FormulaR1C1 = "Daily Total Logistic Trucks by Hour" Range("Q2").Select Columns("Q:Q").EntireColumn.AutoFit Range("Q2").Select ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""0"")" Range("Q2").Select Selection.AutoFill Destination:=Range("Q2:Q25"), Type:=xlFillDefault Range("Q2:Q25").Select 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("Q13").Select Selection.AutoFill Destination:=Range("Q13:Q25"), Type:=xlFillDefault Range("Q13:Q25").Select 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"")" Range("Q26").Select 'Format the Daily Average Number of Logistic Trucks column to fit the data Range("R1").Select ActiveCell.FormulaR1C1 = "Daily Average Number of Logistic Trucks by Hour" Range("R2").Select Columns("R:R").ColumnWidth = 8.86 Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=AVERAGE(R2C17:R25C17)" Range("R2").Select Selection.AutoFill Destination:=Range("R2:R25") Range("R2:R25").Select 'Format the Average Time of Logistic Trucks' Trip by Hour column to fit the data Range("S1").Select ActiveCell.FormulaR1C1 = "Daily Average Time of Logistic Trucks Trips by Hour" Range("S2").Select Columns("S:S").EntireColumn.AutoFit 'Format the time format in the S column Range("S2").Select Columns("S:S").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=AVERAGEIF(C[-5],RC[-3],C[-6])" Range("S2").Select Selection.AutoFill Destination:=Range("S2:S25"), Type:=xlFillDefault Range("S2:S25").Select Selection.NumberFormat = "h:mm;@" 'Format the S column to fit the data and fill the S column with the formula in cell S2 Range("S2").Select Columns("S:S").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=AVERAGEIF(C[-5],RC[-3],C[-6])" Range("S2").Select Selection.AutoFill Destination:=Range("S2:S25"), Type:=xlFillDefault Range("S2:S25").Select Selection.NumberFormat = "h:mm;@" 'Format the S column to change all error messages to the time "0:00" Set MyRange4 = Range("S2:S25") For Each MyCell4 In MyRange4 If IsError(MyCell4) Then MyCell4.Value = "0:00" End If Next MyCell4 Range("$S$2:$S$25").Font.Name = "Calibri" Range("$S$2:$S$25").Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone 'Format the S column to change all erorro message to the time "0:00" Set MyRange5 = Range("S2:S25") For Each MyCell5 In MyRange4 If IsError(MyCell5) Then MyCell5.Value = "0:00" End If Next MyCell5 Range("$S$2:$S$25").Font.Name = "Calibri" Range("$S$2:$S$25").Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone 'Enter the title "Daily Average Time Logistic Trucks" into cell T1 and format the column to fit the data Range("T1").Select ActiveCell.FormulaR1C1 = "Daily Average Time of Logistic Trucks by Hour" Range("T2").Select Columns("T:T").EntireColumn.AutoFit 'If there is a zero in the Daily Average Logistic Trucks column do not include it in the average for the day Range("T1") = "Daily Average Time of Logistic Trucks by Hour" Range("T2").Select Columns("T:T").EntireColumn.AutoFit Range("T2").Select ActiveCell.FormulaR1C1 = "=AVERAGEIF(R2C19:R25C19, ""<> 0"")" Range("T3").Select ActiveWindow.ScrollColumn = 1 ActiveWindow.SmallScroll ToRight:=9 Range("T2").Select Selection.AutoFill Destination:=Range("T2:T25") Range("T2:T25").Select Selection.NumberFormat = "h:mm;@" 'Close the Program End Sub |
| Excerpt | |
| Footnotes |