Military Time Converting wrong

S

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
Hello,
I have a macro (below) that works as it should. However, if I put the time in (using a colon), so 12:00 instead of 1200, it gives me a different time (0:00). Any ideas? I need it to work both sides.

Code: 
Public Function MilitaryToTime(T1 As Integer)'
' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.
'
Dim TT1 As Double


TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
MilitaryToTime = TT1


End Function

Thanks
 
Sort by date Sort by votes
Rick-
I am sorry to bother you with that. I'm not sure what happened. So it isn't the first time I've had a problem similar to this but this one I was able to replicate.

I put an apostrophe in front of all three lines of the "militarytoTime" function lines (to essentially take them out of service without deleting) and then re-ran my sheet creators. Worked. I already knew that my "Arrival" sheet creator worked with the function in place and the "Noon" creator was giving me trouble. Anyway- the first formula that used the function was shared on both creators so it wasn't that. I saved the workbook, closed it, then opened it back up and ran it all again with the function re-enabled (apostrophes gone) and it worked as intended. I'm not totally sure why this happened but your help is invaluable.
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
hmmm. Error'd again. Same piece.

Runtime 13 Type Mismatch and it highlghts the militarytotime = format etc piece.

Each time. I'm checking against what you had in this thread just to check but I don't see a difference. It was working seamlessly and now it errors- by the way it's generating the error and debugging while trying to make a noon sheet- which has this embedded in some of the formulas.

Thanks

Code: 
Function MilitarytoTime(Miltime As String) As Date  
     MilitarytoTime = Format(Replace(Miltime, ":", ""), "00:00")
End Function
 
Last edited:
Upvote 0
Also- this is the "Noon sheet creator" that I run and causes the debugger to stop (stops each time around the ETA time formula being inserted- near the bottom formulas are put in)
Figured this might help

Code: 
Sub AddNoonSheet()
'Speeds up the formating part
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False


'Error Checks for a Noon Sheet already existing
    If SheetExists("Voyage Specifics") Then
        If SheetExists("Noon") Then
            MsgBox "Noon Sheet already exists, would you like to delete it and start over?", vbYesNoCancel
            If vbYes Then
                Sheets("Noon").Delete
                Call NoonSheetCreate
            ElseIf vbNo Then
                Sheets("Noon").Select
                Exit Sub
            End If
        Else: Call NoonSheetCreate
        End If
    Else: MsgBox "You must create a Voyage Specifics Sheet first. Would you like to create the sheet?", vbYesNoCancel
        If vbYes Then
            Call VoyageSpecifics
        ElseIf vbNo Then
            Exit Sub
        End If
    End If
    'Speeds up the formating part
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub
'Begins the Noon Sheet Creator
Private Sub NoonSheetCreate()
    'Speeds up the formating part
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    Sheets.Add(After:=Sheets("Voyage Specifics")).Name = "Noon"


'This creates all of the proper row heights/column widths
  Rows("1:55").RowHeight = 15
    Columns("A:A").ColumnWidth = 3.29
    Columns("B:B").ColumnWidth = 1.43
    Columns("C:C").ColumnWidth = 5.57
    Columns("D:D").ColumnWidth = 9.14
    Columns("E:G").ColumnWidth = 2.8
    Columns("H:H").ColumnWidth = 7.29
    Columns("I:L").ColumnWidth = 8.43
    Columns("M:M").ColumnWidth = 9.14
    Columns("N:N").ColumnWidth = 7.86
    Columns("O:O").ColumnWidth = 3.14
    Columns("P:P").ColumnWidth = 4
    Columns("Q:Q").ColumnWidth = 20
    Columns("R:R").ColumnWidth = 8.5
    Columns("S:S").ColumnWidth = 2.5
    Columns("T:T").ColumnWidth = 8
    Columns("U:U").ColumnWidth = 2
    Columns("V:V").ColumnWidth = 13
    Columns("W:W").ColumnWidth = 5
    Columns("X:X").ColumnWidth = 3
    Columns("Y:Y").ColumnWidth = 6
    Columns("Z:Z").ColumnWidth = 3.5
    
'This creates the appropriate printer border sheets and daily inputs
'Company Title
    Range("A1:O1,A20:O20,A39:O39").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Vessel Name
    Range("A2:O2,A21:O21,A40:O40").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Noon/Arrival Report
    Range("A3:O3,A22:O22,A41:O41").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Time/Date
    Range("A4:C4,A23:C23,A42:C42").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'ZD
    Range("C5,C24,C43").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'Date
    Range("F4:H4,F23:H23,F42:H42").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Port + Selected Port
    Range("I4:J4,I23:J23,I42:J42").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Selected Port
    Range("I5:J5,I24:J24,I43:J43").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Voyage #, Course, Speed, Trip Distance, Total Distance, TAS, Spd Req, Voyage Total Dist
    Range("L4:M11,L23:M30,L42:M49").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge True
'Latitude, Longitude, Hours Run, Total Hours, DTG, ETA, Wind, Wave, Swell, Temp, Pressure
    Range("A6:C16,A25:C35,A44:C54").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge True
'Latitude & Longitude Left Alignment
    Range("D6,D7,D25,D26,D44,D45").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'ETA Date
    Range("F11:H11,F30:H30,F49:H49").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Heavy Weather
    Range("J12:L13,J31:L32,J50:L51").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Slow Steaming
    Range("J14:L15,J33:L34,J52:L53").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Watch Officer
    Range("A17:I17,A36:I36,A55:I55").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Input in Yellow
    Range("Q4:S4").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'ETA Calculators
    Range("Q27:T27").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("Q29:R29,Q31:R31,Q32:R32").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("S28,S29:T29,S31:T31,S32:T32").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("P28:P29,P31:P32").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Voyage Report Indicators
    Range("V20:Y22").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge True
'Override Inputs and Voyage Report Indicators
    Range("Z20,Z21,Z22,V23:Z23,W24:Z24,W25:Z25,X28:Y28,X29:Y29,X30:Y30,X31:Y31,X32:Y32,X33,Y33:Z33").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("V28:W28,V29:W29,V30:W30,V31:W31,V32:W32,V34:X34").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("Y28").Select
'Borders out the Noon Report Cells
    Range("O1:O17,I12:I15,O20:O36,I31:I34,O39:O55,I50:I53").Activate
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
     Range("O1:O17,O20:O36,O39:O55").Activate
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
    Range("A1:A17,A20:A36,A39:A55").Activate
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
   
    Range("A17:O17,A36:O36,A55:O55,J13:O13,J32:O32,J51:O51,J15:O15,J34:O34,J53:O53").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
    Range("A3:O3,A22:O22,A41:O41,A5:E5,A6:E6,A24:E24,A25:E25,A43:E43,A44:E44,A45:O45,A7:O7,A26:O26,A28:O28,A30:O30,A47:O47,A9:O9,A11:O11,A49:O49,M12:O12,M31:O31,M33:O33,M14:O14,M50:O50,M52:O52").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    Range("E6:E7,E25:E26,E44:E45").Select
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'Borders out the Input in Yellow Cells
    Range("R5:R16,R19:R20,R23:R24,S6:S7").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'Borders out the ETA Calculators
Range("Q27:T29,Q31:T32").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'Borders out the Voyage Report Indicators
    Range("V20:Z22").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'Borders out the Manual Input Cells
    Range("V23:Z34").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'Colors Voyage Report Indicators
    If Range("Z20") = "Yes" Then
        Interior.Color = 5287936
    ElseIf Range("Z20") = "No" Then
        Range("Z20").Interior.Color = vbRed
    End If
    If Range("Z21") = "Yes" Then
        Interior.Color = 5287936
    ElseIf Range("Z21") = "No" Then
        Range("Z21").Interior.Color = 15773696
    End If
    If Range("Z22") = "L" Then
        Interior.Color = 5287936
    ElseIf Range("Z22") = "B" Then
        Range("Z22").Interior.Color = 15773696
    End If
        
        
    
    Range("H12:H15,H31:H34,H50:H53").Borders(xlEdgeRight).LineStyle = xlNone


'Hides the Manual Inputs
    Range("V23:Z34").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("V24:Z34").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
     
'Creates Fonts for Entire Sheet
   'Font for Company
    
    Range("A1:O1,A20:O20,A39:O39").Select
    With Selection.Font
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "9"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With


   'Font for Vessel Name
    Range("A2:O2,A21:O21,A40:O40").Select
    With Selection.Font
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
   'Font for Noon/Arrival
    Range("A3:O3,A22:O22,A41:O41").Select
    With Selection.Font
        .Bold = True
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "14"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    'Font for Rest of the Sheet
    Range("A4:O17,A23:O36,A42:O55,P1:Z55").Select
    With Selection.Font
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With


    'Font for Input in Yellow
    Range("Q4:S4").Select
    With Selection.Font
        .Underline = xlUnderlineStyleSingle
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    'Font for Heavy Weather and Slow Steaming
    Range("Q18,Q22").Select
    With Selection.Font
        .Underline = xlUnderlineStyleSingle
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
   
   'Font for Rest of the Sheet
    Range("P1:Z17,P18:P55,R18:Z55,Q19:Q21,Q23:Q55").Select
    With Selection.Font
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    'Bold for Voyage Report Indicators and Manual Inputs
     Range("Z20:Z22,V23:Z23").Select
    With Selection.Font
        .Bold = True
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    'Aligns ETA Dates
    Range("E11,E30,E49").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("F11:H11,F30:H30,F49:H49").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    
    'Aligns Weather Reports
     Range("D12:D16,D31:D35,D50:D54").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    'Aligns Inputs in Yellow
    Range("R5").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("R6:R16").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With


    
' Inputs all of the Cell Data for the Noon Report
'


'All the formula titles
    Range("A1:O1,A20:O20,A39:O39").FormulaR1C1 = "OSG Ship Management Inc."
    Range("A2:O2,A21:O21,A40:O40").FormulaR1C1 = "=Notes!R22C11"
    Range("A3:O3,A22:O22,A41:O41").FormulaR1C1 = "Noon Report"
    Range("A4:C4,A23:C23,A42:C42").FormulaR1C1 = "Time/Date:"
    Range("E4,E23,E42").FormulaR1C1 = "/"
    Range("A5,A24,A43").FormulaR1C1 = "ZD:"
    Range("A6:C6,A25:C25,A44:C44").FormulaR1C1 = "Latitude:"
    Range("A7:C7,A26:C26,A45:C45").FormulaR1C1 = "Longitude:"
    Range("A8:C8,A27:C27,A46:C46").FormulaR1C1 = "Hours Run:"
    Range("E8,E27,E46").FormulaR1C1 = "h"
    Range("G8,G27,G46").FormulaR1C1 = "m"
    Range("A9:C9,A28:C28,A47:C47").FormulaR1C1 = "Total Hours:"
    Range("E9,E28,E47").FormulaR1C1 = "h"
    Range("G9,G28,G47").FormulaR1C1 = "m"
    Range("A10:C10,A29:C29,A48:C48").FormulaR1C1 = "DTG:"
    Range("E10,E29,E48").FormulaR1C1 = "nm"
    Range("A11:C11,A30:C30,A49:C49").FormulaR1C1 = "ETA:"
    Range("E11,E30,E49").FormulaR1C1 = "/"
    Range("A12:C12,A31:C31,A50:C50").FormulaR1C1 = "Wind:"
    Range("A13:C13,A32:C32,A51:C51").FormulaR1C1 = "Wave:"
    Range("A14:C14,A33:C33,A52:C52").FormulaR1C1 = "Swell:"
    Range("A15:C15,A34:C34,A53:C53").FormulaR1C1 = "Temp:"
    Range("A16:C16,A35:C35,A54:C54").FormulaR1C1 = "Pressure:"
    Range("A17:I17,A36:I36,A55:I55").FormulaR1C1 = "Watch Officer"
    Range("I4:J4,I23:J23,I42:J42").FormulaR1C1 = "Arrival Port:"
    Range("L4:M4,L23:M23,L42:M42").FormulaR1C1 = "Voyage:"
    Range("L5:M5,L24:M24,L43:M43").FormulaR1C1 = "Course:"
    Range("O5,O24,O43").FormulaR1C1 = "°"
    Range("L6:M6,L25:M25,L44:M44").FormulaR1C1 = "Speed:"
    Range("O6,O25,O44").FormulaR1C1 = "kts"
    Range("L7:M7,L26:M26,L45:M45").FormulaR1C1 = "Trip Distance:"
    Range("O7,O26,O45").FormulaR1C1 = "nm"
    Range("L8:M8,L27:M27,L46:M46").FormulaR1C1 = "Total Distance:"
    Range("O8,O27,O46").FormulaR1C1 = "nm"
    Range("L9:M9,L28:M28,L47:M47").FormulaR1C1 = "TAS:"
    Range("O9,O28,O47").FormulaR1C1 = "kts"
    Range("L10:M10,L29:M29,L48:M48").FormulaR1C1 = "Spd Req:"
    Range("O10,O29,O48").FormulaR1C1 = "kts"
    Range("L11:M11,L30:M30,L49:M49").FormulaR1C1 = "Voyage Tot Dist:"
    Range("O11,O30,O49").FormulaR1C1 = "nm"
    Range("J12:L13,J31:L32,J50:L51").FormulaR1C1 = "Heavy Weather:"
    Range("J14:L15,J33:L34,J52:L53").FormulaR1C1 = "Slow Steaming:"
    Range("L12:M12,L31:M31,L50:M50").FormulaR1C1 = "Time:"
    Range("O12,O31,O50").FormulaR1C1 = "hrs"
    Range("L13:M13,L32:M32,L51:M51").FormulaR1C1 = "Distance:"
    Range("O13,O32,O51").FormulaR1C1 = "nm"
    Range("L14:M14,L33:M33,L52:M52").FormulaR1C1 = "Time:"
    Range("O14,O33,O52").FormulaR1C1 = "hrs"
    Range("L15:M15,L34:M34,L53:M53").FormulaR1C1 = "Distance:"
    Range("O15,O34,O53").FormulaR1C1 = "nm"
    
    Range("Q4:S4").FormulaR1C1 = "Input in Yellow"
    Range("Q5").FormulaR1C1 = "Zone Description:"
    Range("Q6").FormulaR1C1 = "Latitude"
    Range("S6").FormulaR1C1 = "N"
    Range("Q7").FormulaR1C1 = "Longitude"
    Range("S7").FormulaR1C1 = "W"
    Range("Q8").FormulaR1C1 = "Distance to Go"
    Range("S8").FormulaR1C1 = "nm"
    Range("Q9").FormulaR1C1 = "Total Distance"
    Range("S9").FormulaR1C1 = "nm"
    Range("Q10").FormulaR1C1 = "Course:"
    Range("S10").FormulaR1C1 = "°"
    Range("Q11").FormulaR1C1 = "Wind(Dir & Force)"
    Range("Q12").FormulaR1C1 = "Wave(Dir & State):"
    Range("Q13").FormulaR1C1 = "Swell(Dir & Descr):"
    Range("Q14").FormulaR1C1 = "Temperature:"
    Range("S14").FormulaR1C1 = "°"
    Range("Q15").FormulaR1C1 = "Pressure:"
    Range("S15").FormulaR1C1 = "mb"
    Range("Q16").FormulaR1C1 = "Watch Officer:"


    Range("Q18").FormulaR1C1 = "Heavy Weather"
    Range("Q19").FormulaR1C1 = "Time:"
    Range("S19").FormulaR1C1 = "hrs"
    Range("Q20").FormulaR1C1 = "Distance:"
    Range("S20").FormulaR1C1 = "nm"
    
    Range("Q22").FormulaR1C1 = "Slow Steaming:"
    Range("Q23").FormulaR1C1 = "Time:"
    Range("S23").FormulaR1C1 = "hrs"
    Range("Q24").FormulaR1C1 = "Distance:"
    Range("S24").FormulaR1C1 = "nm"
    
    Range("P28:P29").FormulaR1C1 = "'1."
    Range("P31:P32").FormulaR1C1 = "'2."
    
    Range("Q27:T27").FormulaR1C1 = "ETA Calculators"
    Range("Q28").FormulaR1C1 = "Desired Arr Date/Time:"
    Range("S28").FormulaR1C1 = "/"
    Range("Q29:R29").FormulaR1C1 = "Mileage(If Not Today's DTG):"
    Range("Q31:R31").FormulaR1C1 = "Anticipated Average Speed:"
    Range("Q32:R32").FormulaR1C1 = "Mileage(If Not Today's DTG):"
    
    Range("V20:Y20").FormulaR1C1 = "Exact Route Calculator:"
    Range("V21:Y21").FormulaR1C1 = "Daylight Savings In Effect:"
    Range("V22:Y22").FormulaR1C1 = "Voyage Loaded/Ballast:"
    
    Range("V23:Z23").FormulaR1C1 = "Override Inputs"
    Range("V24").FormulaR1C1 = "Time:"
    Range("V25").FormulaR1C1 = "Date:"
    Range("V26").FormulaR1C1 = "Hours Run:"
    Range("X26").FormulaR1C1 = "hrs"
    Range("Z26").FormulaR1C1 = "min"
    Range("V27").FormulaR1C1 = "Total Hours:"
    Range("X27").FormulaR1C1 = "hrs"
    Range("Z27").FormulaR1C1 = "min"
    Range("V28").FormulaR1C1 = "Speed:"
    Range("Z28").FormulaR1C1 = "kts"
    Range("V29:W29").FormulaR1C1 = "Trip Distance:"
    Range("Z29").FormulaR1C1 = "nm"
    Range("V30:W30").FormulaR1C1 = "Total Distance:"
    Range("Z30").FormulaR1C1 = "nm"
    Range("V31:W31").FormulaR1C1 = "Total Average Speed:"
    Range("Z31").FormulaR1C1 = "kts"
    Range("V32").FormulaR1C1 = "Speed Required:"
    Range("Z32").FormulaR1C1 = "kts"
    Range("V33").FormulaR1C1 = "New ETA:"
    Range("X33").FormulaR1C1 = "/"
    Range("V34:W34").FormulaR1C1 = "New Voyage Distance:"
    Range("Z34").FormulaR1C1 = "nm"
    
         
    
'All of the equals for the static cells
'ZD + or -
    Range("B5").FormulaR1C1 = "=IF(R[2]C[3]=""W"",""-"",""+"")"
    Range("B24").FormulaR1C1 = "=R[-19]C"
    Range("B43").FormulaR1C1 = "=R[-19]C"
'12:00 Time
    Range("D4").FormulaR1C1 = "=IF(R[20]C[19]="""",TIME(12,0,0),MilitaryToTime(R[20]C[19]))"
    Range("D23").FormulaR1C1 = "=R[-19]C"
    Range("D42").FormulaR1C1 = "=R[-19]C"
'Date Repeater
    Range("F4:H4").FormulaR1C1 = "=IF(R[21]C[17]="""",""No Data Input"",R[21]C[17])"
    Range("F23:H23").FormulaR1C1 = "=R[-19]C"
    Range("F42:H42").FormulaR1C1 = "=R[-19]C"
'ZD = R5
    Range("C5").FormulaR1C1 = "=RC[15]"
    Range("C24").FormulaR1C1 = "=R[-19]C[15]"
    Range("C43").FormulaR1C1 = "=R[-38]C[15]"
'Latitude = R6
    Range("D6").FormulaR1C1 = "=RC[14]"
    Range("D25").FormulaR1C1 = "=R[-19]C[14]"
    Range("D44").FormulaR1C1 = "=R[-38]C[14]"
'N or S
    Range("E6").FormulaR1C1 = "=RC[14]"
    Range("E25").FormulaR1C1 = "=R[-19]C"
    Range("E44").FormulaR1C1 = "=R[-19]C"
'Longtitude = R7
    Range("D7").FormulaR1C1 = "=RC[14]"
    Range("D26").FormulaR1C1 = "=R[-19]C[14]"
    Range("D45").FormulaR1C1 = "=R[-38]C[14]"
'E or W
    Range("E7").FormulaR1C1 = "=RC[14]"
    Range("E26").FormulaR1C1 = "=R[-19]C"
    Range("E45").FormulaR1C1 = "=R[-19]C"
'Hours Run
    Range("D8").FormulaR1C1 = "=IF(R[-4]C[2]=""No Data Input"",0,IF(R[18]C[19]="""",((INT(ABS((((((R[-4]C+((IF(R[-1]C[1]=""E"",-TIME(R[-3]C[14],0,0),TIME(R[-3]C[14],0,0)))))+R[-4]C[2])-((MilitaryToTime('Voyage Specifics'!R[-2]C[-1])+((IF((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R[-5]C[-2]:R[44]C[5],8,FALSE))<0,-TIME(-((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1" & _
        ",0))),0,0),TIME(((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1,0))),0,0)))))+'Voyage Specifics'!R[-1]C[-1])))))))*24)+(HOUR(ABS((((((R[-4]C+((IF(R[-1]C[1]=""E"",-TIME(R[-3]C[14],0,0),TIME(R[-3]C[14],0,0)))))+R[-4]C[2])-((MilitaryToTIme('Voyage Specifics'!R[-2]C[-1])+((IF((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R[-5]C[-2]:R[44]C[5]," & _
        "8,FALSE))<0,-TIME(-((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1,0))),0,0),TIME(((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1,0))),0,0)))))+'Voyage Specifics'!R[-1]C[-1]))))))),R[18]C[19]))" & _
        ""
    Range("D27").FormulaR1C1 = "=R[-19]C"
    Range("D46").FormulaR1C1 = "=R[-19]C"
'Min Run
    Range("F8").FormulaR1C1 = "=IF(R[-4]C=""No Data Input"",0,IF(R[18]C[19]="""",(MINUTE(ABS((((((R[-4]C[-2]+((IF(R[-1]C[-1]=""E"",-TIME(R[-3]C[12],0,0),TIME(R[-3]C[12],0,0)))))+R[-4]C)-((MilitaryToTime('Voyage Specifics'!R[-2]C[-3])+((IF((VLOOKUP('Voyage Specifics'!R[-3]C[-3],Ports!R[-5]C[-4]:R[44]C[3],8,FALSE))<0,-TIME(-((VLOOKUP('Voyage Specifics'!R[-3]C[-3],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[20]=""Yes"",-" & _
        "1,0))),0,0),TIME(((VLOOKUP('Voyage Specifics'!R[-3]C[-3],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[20]=""Yes"",-1,0))),0,0)))))+'Voyage Specifics'!R[-1]C[-3]))))))),R[18]C[19]))" & _
        ""
    Range("F27").FormulaR1C1 = "=R[-19]C"
    Range("F46").FormulaR1C1 = "=R[-19]C"
'Total Hours Run
    Range("D9").FormulaR1C1 = "=IF(R[18]C[19]="""",R[-1]C,R[18]C[19])"
    Range("D28").FormulaR1C1 = "=R[-19]C"
    Range("D47").FormulaR1C1 = "=R[-19]C"
'Total Min Run
    Range("F9").FormulaR1C1 = "=IF(R[18]C[19]="""",R[-1]C,R[18]C[19])"
    Range("F28").FormulaR1C1 = "=R[-19]C"
    Range("F47").FormulaR1C1 = "=R[-19]C"
'DTG =
    Range("D10").FormulaR1C1 = "=R[-2]C[14]"
    Range("D29").FormulaR1C1 = "=R[-19]C"
    Range("D48").FormulaR1C1 = "=R[-19]C"
'ETA = Time
    Range("D11").FormulaR1C1 = "=IF(R[22]C[19]="""",MilitaryToTime('Voyage Specifics'!R[-2]C[-1]),MilitaryToTime(R[22]C[19]))"
    Range("D30").FormulaR1C1 = "=R[-19]C"
    Range("D49").FormulaR1C1 = "=R[-19]C"
'ETA = Date
    Range("F11:H11").FormulaR1C1 = "=IF(R[22]C[19]="""",'Voyage Specifics'!R[-1]C[-3],R[22]C[19])"
    Range("F30:H30").FormulaR1C1 = "=R[-19]C"
    Range("F49:H49").FormulaR1C1 = "=R[-19]C"
'Wind = R11
    Range("D12").FormulaR1C1 = "=R[-1]C[14]"
    Range("D31").FormulaR1C1 = "=R[-19]C"
    Range("D50").FormulaR1C1 = "=R[-19]C"
'Wave = R12
    Range("D13,D32,D51").FormulaR1C1 = "=R[-1]C[14]"
    Range("D32").FormulaR1C1 = "=R[-19]C"
    Range("D51").FormulaR1C1 = "=R[-19]C"
'Swell = R13
    Range("D14").FormulaR1C1 = "=R[-1]C[14]"
    Range("D33").FormulaR1C1 = "=R[-19]C"
    Range("D52").FormulaR1C1 = "=R[-19]C"
'Temp = R14
    Range("D15").FormulaR1C1 = "=R[-1]C[14]"
    Range("D34").FormulaR1C1 = "=R[-19]C"
    Range("D53").FormulaR1C1 = "=R[-19]C"
'Pressure = R15
    Range("D16").FormulaR1C1 = "=R[-1]C[14]"
    Range("D35").FormulaR1C1 = "=R[-19]C"
    Range("D54").FormulaR1C1 = "=R[-19]C"
'Officer
    Range("J17").FormulaR1C1 = "=R[-1]C[8]"
    Range("J36").FormulaR1C1 = "=R[-19]C"
    Range("J55").FormulaR1C1 = "=R[-19]C"
'Selected Arrival
    Range("I5:J5").FormulaR1C1 = "=IF('Voyage Specifics'!R[3]C[-6]="""",""No Port Selected"",'Voyage Specifics'!R[3]C[-6])"
    Range("I24:J24").FormulaR1C1 = "=R[-19]"
    Range("I43:J43").FormulaR1C1 = "=R[-19]"
'Voyage #
    Range("N4").FormulaR1C1 = "='Voyage Specifics'!RC[-11]"
    Range("N23").FormulaR1C1 = "=R[-19]"
    Range("N42").FormulaR1C1 = "=R[-19]"
'Course
    Range("N5").FormulaR1C1 = "=R[5]C[4]"
    Range("N24").FormulaR1C1 = "=R[-19]C"
    Range("N43").FormulaR1C1 = "=R[-19]C"
'Speed
    Range("N6").FormulaR1C1 = "=IF((R[22]C[9]=""""),(R[1]C[0]/(R[2]C[-10]+(R[2]C[-8]/60))),R[22]C[9])"
    Range("N25").FormulaR1C1 = "=R[-19]C"
    Range("N44").FormulaR1C1 = "=R[-19]C"
'Trip Distance
    Range("N7").FormulaR1C1 = "=IF(R[22]C[10]="""",R[1]C[0],R[22]C[10])"
    Range("N26").FormulaR1C1 = "=R[-19]C"
    Range("N45").FormulaR1C1 = "=R[-19]C"
'Total Distance
    Range("N8").FormulaR1C1 = "=IF(R[22]C[10]="""",IF(R[12]C[12]=""Yes"",R[3]C-R[2]C[-10],R[1]C[4]+'Voyage Specifics'!R[4]C[-11]),R[22]C[10])"
    Range("N27").FormulaR1C1 = "=R[-19]C"
    Range("N46").FormulaR1C1 = "=R[-19]C"
'TAS
    Range("N9").FormulaR1C1 = "=IF(R[22]C[11]="""",(R[-1]C/(RC[-10]+(RC[-8]/60))),R[22]C[11])"
    Range("N28").FormulaR1C1 = "=R[-19]C"
    Range("N47").FormulaR1C1 = "=R[-19]C"
'Spd Req
    Range("N10").FormulaR1C1 = "=IF(R[22]C[10]="""",(RC[-10]/(((R[1]C[-8]+R[1]C[-10]+(TIME(IF(R[11]C[12]=""Yes"",(VLOOKUP(R[-5]C[-5],Ports!R[-7]C[-12]:R[42]C[-4],8,FALSE))-1,(VLOOKUP(R[-5]C[-5],Ports!R[-7]C[-12]:R[42]C[-4],8,FALSE))),0,0)))-(R[-6]C[-8]+R[-6]C[-10]+(TIME(R[-5]C[-11],0,0))))*24)),R[22]C[10])"
    Range("N29").FormulaR1C1 = "=R[-19]C"
    Range("N48").FormulaR1C1 = "=R[-19]C"
'Voyage Tot Dist
    Range("N11").FormulaR1C1 = "=(IF(R[23]C[11]<>"""",R[23]C[11],'Voyage Specifics'!RC[-11]))"
    Range("N30").FormulaR1C1 = "=R[-19]C"
    Range("N49").FormulaR1C1 = "=R[-19]C"
'Heavy Weather Time
    Range("N12").FormulaR1C1 = "=IF(R[7]C[4]<=(R[-4]C[-10]+R[-4]C[-8]),R[7]C[4],""Error"")"
    If IsNumeric(Range("D8").Value) Then
        If Range("R19").Value > (Range("D8").Value + (Range("F8").Value / 60)) Then
            Range("N12").Value = "Error"
        End If
    End If
    Range("N31").FormulaR1C1 = "=R[-19]C"
    Range("N50").FormulaR1C1 = "=R[-19]C"
'Heavy Weather Distance
    Range("N13").FormulaR1C1 = "=R[7]C[4]"
    Range("N32").FormulaR1C1 = "=R[-19]C"
    Range("N51").FormulaR1C1 = "=R[-19]C"
'Slow Steaming Time
    Range("N14").FormulaR1C1 = "=IF(R[9]C[4]<=(R[-6]C[-10]+R[-6]C[-8]),R[9]C[4],""Error"")"
    If IsNumeric(Range("D8").Value) Then
        If Range("R23").Value > (Range("D8").Value + (Range("F8").Value / 60)) Then
            Range("N14").Value = "Error"
        End If
    End If
    Range("N33").FormulaR1C1 = "=R[-19]C"
    Range("N52").FormulaR1C1 = "=R[-19]C"
'Slow Steaming Distance
    Range("N15").FormulaR1C1 = "=R[9]C[4]"
    Range("N34").FormulaR1C1 = "=R[-19]C"
    Range("N53").FormulaR1C1 = "=R[-19]C"


'Voyage Report Indicators
    Range("Z20").FormulaR1C1 = "=IF('Voyage Specifics'!R[-4]C[-20]=""Exact Route Calculator Enabled"",""Yes"",""No"")"
    Range("Z21").FormulaR1C1 = "=IF(AND((Notes!R[-8]C[-14]+Notes!R[-8]C[-13])<((IF(R[-17]C[-20]=""No Data Input"",'Voyage Specifics'!R[-15]C[-23]+'Voyage Specifics'!R[-14]C[-23],R[-17]C[-20]+R[-17]C[-22]))),(Notes!R[-7]C[-14]+Notes!R[-7]C[-13])>((IF(R[-17]C[-20]=""No Data Input"",'Voyage Specifics'!R[-15]C[-23]+'Voyage Specifics'!R[-14]C[-23],R[-17]C[-20]+R[-17]C[-22])))),""Yes"",""No"")"
    Range("Z22").FormulaR1C1 = "=IF('Voyage Specifics'!R[-14]C[-20]=""Loaded"",""L"",""B"")"
   
'Manual Input Clearing
    Range("R5:R16,R19,R20,R23,R24").ClearContents
'Calls Button Macro
  
    Dim w As Worksheet
        Dim a As Button
        Dim b As Button
        Dim c As Button
        Dim d As Button
        Dim e As Button
        Dim f As Button
        Dim g As Button
        Dim h As Button
        Dim j As Button
        Set w = ActiveSheet
   'Print
        Set a = w.Buttons.Add(840, 10, 108, 30)
        a.OnAction = "Print_Sheet"
        a.Characters.Text = "Print"
    'Save
        Set b = w.Buttons.Add(840, 50, 108, 30)
        b.OnAction = "Save_As"
        b.Characters.Text = "Save & Quit"
    'Modify Voyage Specifics
        Set c = w.Buttons.Add(840, 90, 108, 30)
        c.OnAction = "Modify_Voyage"
        c.Characters.Text = "Modify Voyage Specifics"
    'Reset Sheet
        Set d = w.Buttons.Add(840, 130, 108, 30)
        d.OnAction = "NoonDel"
        d.Characters.Text = "Reset Sheet"
    'Recalculate Sheet
        Set e = w.Buttons.Add(840, 170, 108, 30)
        e.OnAction = "Recalc_Sheet"
        e.Characters.Text = "Recalculate Sheet"
        
    'Manual Inputs
        Set f = w.Buttons.Add(840, 210, 108, 30)
        f.OnAction = "ManualCalcShow"
        f.Characters.Text = "Manual Inputs"
        
    'ETA Calculator
        Set f = w.Buttons.Add(840, 250, 108, 30)
        f.OnAction = "ETA_CalcShow"
        f.Characters.Text = "Show ETA Calculator"
        
    'ETA Calculator Calculate
        Set g = w.Buttons.Add(568, 490, 108, 30)
        g.OnAction = "ETA_CALC1"
        g.Characters.Text = "ETA Calculator 1"
        g.Visible = False
            
    'ETA Calculator Calculate
        Set h = w.Buttons.Add(688, 490, 108, 30)
        h.OnAction = "ETA_CALC2"
        h.Characters.Text = "ETA Calculator 2"
        h.Visible = False


    'Hide ETA/Manual Inputs
        Set j = w.Buttons.Add(825, 515, 150, 30)
        j.OnAction = "ETACALCHIDE"
        j.Characters.Text = "Hide Manual Inputs/ETA Calculators"
        j.Visible = False
        
        
        
'Cell Formats
    Range("D4,D23,D42").NumberFormat = "h:mm;@"
    Range("F4:H4,F23:H23,F42:H42").NumberFormat = "dd-mmm-yy"
    Range("D11,D30,D49").NumberFormat = "h:mm;@"
    Range("F11:H11,F30:H30,F49:H49").NumberFormat = "dd-mmm-yy"
    Range("N6,N25,N44").NumberFormat = "0.0"
    Range("N9,N28,N47").NumberFormat = "0.0"
    Range("N10,N29,N48").NumberFormat = "0.00"
    Range("N11,N30,N49").NumberFormat = "0.0"
    Range("W33").NumberFormat = "@"
    Range("Y33:Z33").NumberFormat = "[$-409]d-mmm-yy;@"
    Range("N5,N24,N43,R10").NumberFormat = "@"
    Range("N5,N24,N43").HorizontalAlignment = xlRight
    Range("R28").NumberFormat = "@"


'Conditional Formatting
    With Cells(20, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""Yes""")
        .Interior.Color = 5287936
        .StopIfTrue = False
    End With
    With Cells(20, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""No""")
        .Interior.Color = vbRed
        .StopIfTrue = False
    End With
    With Cells(21, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""Yes""")
        .Interior.Color = 5287936
        .StopIfTrue = False
    End With
    With Cells(21, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""No""")
        .Interior.Color = 15773696
        .StopIfTrue = False
    End With
    With Cells(22, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""L""")
        .Interior.Color = 5287936
        .StopIfTrue = False
    End With
    With Cells(22, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""B""")
        .Interior.Color = 15773696
        .StopIfTrue = False
    End With
    
'Unlocks Input Cells for Use
    Range( _
        "R5:R16,R19:R20,R23:R24,R28,T28,S29:T29,S31:T31,S32:T32,W24:Z25,W26,W27,Y26,Y27,X28:Y32,W33,Y33:Z33,Y34,S6,S7" _
        ).Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    
'Hides ETA Calculators
    Range("Q27:T27,R28,T28,S29:T29,S31:T31,S32:T32").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("P27:T32").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("Q27:T32").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Locked = True
    Range("R5").Select
    
'Hides Grid Lines
    ActiveWindow.DisplayGridlines = False
    
'Renables the Calculator
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
          
'Zooms to 80%
    ActiveWindow.Zoom = 80
    Range("R5").Select
'Protects Sheet
    ActiveSheet.Protect Password:="1234567890", UserinterFaceOnly:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
 
Last edited:
Upvote 0
@Rick Rothstein

Rich (BB code): 
Function Miltime(T1 As Integer)
' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.


Dim TT1 As Double
TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
Miltime = TT1


End Function
Function MilitarytoTime(Miltime As String) As Date    MilitarytoTime = Format(Replace(Miltime, ":", ""), "00:00")
End Function
This is the formula that is where she gets stuck if there is no variable in "Voyage Specifics" when running my sheet creator
Rich (BB code): 
Range("D11").FormulaR1C1 = "=IF(R[22]C[19]="""",MilitaryToTime('Voyage Specifics'!R[-2]C[-1]),MilitaryToTime(R[22]C[19]))"

Good Evening- Working perfectly if applied after everything is created but if the sheet creator (which writes formulas and formats via vba) is run, it errors the red line above each time. So, I put an apostrophe in front and disable the function, then get it running after the sheet is created, I'm fine.
You asked about what the miltime value- so I checked- If there is no variable (i.e. I haven't put anything in the cell yet) then it errors. If I put a value in the cell and then run my sheet creator macro it's fine....
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Variable = 0 Causing Code failure
Replies
6
Views
520
sassriverrat
S
J
  • Question
Julian Time calculation
Replies
1
Views
971
mole999
mole999
S
  • Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
803
dmt32
D
B
  • Question
WorksheetFunction.Text() Function not Formatting Correctly in VBA
Replies
1
Views
3K
Joe4
Joe4
T
  • Question
Convert half hour to hour using military time
Replies
5
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,216,112
Messages
6,128,901
Members
449,477
Latest member
panjongshing

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top