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”
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’ Trip 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 Logistic Trucks”
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 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;@”
‘Close the Program
End Sub