| You are currently viewing a revision titled "Logistic Macros Weekly Stats (2A)", saved on June 27, 2022 at 10:53 am by Patrick Burcham | |
|---|---|
| Title | Logistic Macros Weekly Stats (2A) |
| 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_Weekly_Stats() '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 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 'Get the times in the C column into the right time format Columns("C:C").Select Set MyRange = Range("$C$2:$C$700") 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 'Get the times in the D column into the right time format Columns("D:D").Select Set MyRange = Range("$D$2:$D$700") 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 'Format the Time Difference column and copy to the formula to the bottom of the column 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:M700") Range("M2:M380").Select Range("O16").Select 'Delete all extra zeros in the M column Dim MyRangeDeleteZero As Range Dim MyCellDeleteZero As Range Set MyRangeDeleteZero = Range("$M$2:$M$700") For Each MyCellDeleteZero In MyRange If MyCellDeleteZero.Value = "00:00:00" Then MyCellDeleteZero.Value = "" End If Next MyCellDeleteZero 'Generate a range for two columns, on the same row if the c column is blank then clean up the m column by deleting the zero Dim TwoColumnsZero As Range, T As Integer Set TwoColumnsZero = Selection With TwoColumnsZero For T = 2 To 700 If IsEmpty(Cells(T, 3).Value) Then Cells(T, 13).Value = "" End If Next T End With 'Format the Time Difference column to fit the data ActiveWindow.SmallScroll Down:=-228 Range("M1").Select ActiveWindow.SmallScroll Down:=-63 ActiveCell.FormulaR1C1 = "Time Difference" Range("M2").Select Columns("M:M").EntireColumn.AutoFit 'Enter the title "Entry Hours" into the N column and format the column 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:N500"), Type:=xlFillDefault Range("N2:N500").Select Range("N201").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=-483 'Generate a range for two columns, on the same row if the C column is blank then delete the value in the N column Dim TwoColumnsN As Range, i2 As Integer Set TwoColumnsN = Selection With TwoColumnsN For i2 = 2 To 700 If Cells(i2, 3) = "" Then Cells(i2, 14).Value = "" End If Next i2 End With 'Enter the term "Daily Hours" title into cell P1 and format the 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 Range("Q1").Select ActiveCell.FormulaR1C1 = "Total Trucks by Hour" 'Enter the term "Weekly Total of Logistic Trucks by Hour" inot cell Q1 and format the column 'to include all hours between 0 and 23 (12 AM and 11 PM) ActiveCell.FormulaR1C1 = "Weekly Total of 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 'Enter the term "Weekly Average Number of Logistic Trucks" into cell R2 and format the column to fit the data Range("R1").Select ActiveCell.FormulaR1C1 = "Weekly Average Number of Logistic Trucks' Trip" 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 "Weekly Average Time of Logistic Trucks' Trip by Hour" column to fit the data Range("S1").Select ActiveCell.FormulaR1C1 = "Weekly Average Time of Logistic Trucks' Trip by Hour" Range("S2").Select Columns("S:S").EntireColumn.AutoFit 'Format the S column to fit the data and format the time to the right format 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 format the time to the right format 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;@" 'If there is an error message in a cell in the range S2 through S25 change it to "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 'If there is an error message in a cell in the range S2 through S25 change it to "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" ange("$S$2:$S$25").Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone 'Format the "Weekly Average Time Logistic Trucks' column to fit the data Range("T1").Select ActiveCell.FormulaR1C1 = "Weekly Average Time Logistic Trucks" Range("T2").Select Columns("T:T").EntireColumn.AutoFit 'Format the "Weekly Average Time Logistic Trucks" colum so that the times are in the right format, also 'if the tme is zero in the S column do not include it in the "Weekly Average Time Logistic Trucks" Range("T1") = "Weekly Average Time Logistic Trucks" 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;@" 'End the Program End Sub |
| Excerpt | |
| Footnotes |