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 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 “Weekly Average Time of Logistic Trucks’ Trips by Hour” column to fit the data
Range(“S1”).Select
ActiveCell.FormulaR1C1 = “Weekly Average Time of Logistic Trucks’ Trips 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 of Logistic Trucks’ by Hour”
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 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;@”
‘End the Program
End Sub