VBA If for selecting value error

[sassriverrat]

Good Evening,

I have an ETA calculator (Time/Distance= Speed) that is giving me an error. Now the error takes me to various other bits of codes like my timestamp macro but it's all based on one part here. The Distance (DTG) below is the cause. If I input data into the "S29" cell, it works perfectly. If I try to rely on it to pull the DTG from D10 when D10 is NOT blank, it will error every time. The purpose of the IF below- take from D10 if S29 is blank and if S29 is not blank, use the value of S29.

Thanks for the help

Sub ETA_CALC1()


Dim Path1 As Date
Dim Path2 As Date
Dim Path3 As Double
Dim Path4 As Double
Dim Path5 As Double
Dim path6 As Double
Dim Path7 As Date
Dim Path8 As Date
Dim DTG As Double
Dim resp As Integer
'This says that the Distance to go should be in D10 but if we want to use a different mileage than what today's report had list, _
(D10 is on the list), we can input into S32 to use our own distance


'This piece ensures "ETA Arrival ZD" is ready
Worksheets("Developer").Range("F3").FormulaR1C1 = "=IF(AND((Notes!R[10]C[6]+Notes!R[10]C[7])<(('Voyage Specifics'!R[6]C[-3]+'Voyage Specifics'!R[7]C[-3])),(Notes!R[11]C[6]+Notes!R[11]C[7])>(('Voyage Specifics'!R[6]C[-3]+'Voyage Specifics'!R[7]C[-3]))),""Yes"",""No"")"


If ActiveSheet.Range("S29").Value = "" Then
    DTG = ActiveSheet.Range("D10").Value
    Else: DTG = ActiveSheet.Range("S29").Value
End If


Path1 = ActiveSheet.Range("R28").Value
Path2 = ActiveSheet.Range("T28").Value
Path3 = DTG


Path5 = Sheets("Developer").Range("G2").Value
path6 = ActiveSheet.Range("C5").Value
Path7 = ActiveSheet.Range("F4").Value
Path8 = ActiveSheet.Range("D4").Value


Path4 = (Path3 / (((Path2 + Path1 + (TimeSerial(Path5, 0, 0))) - (Path7 + Path8 + (TimeSerial(path6, 0, 0)))) * 24))




resp = MsgBox("Based on your desired Arrival Time/Date and your mileage input, your speed required to make your ETA is: " & Round(Path4, 1) & " knots" & vbCrLf & vbCrLf & "Would you like to use this ETA for Today's Report?", vbYesNo)
If resp = vbYes Then
    ActiveSheet.Range("W33").Value = Format(Path1, "hh:mm;@")
    ActiveSheet.Range("Y33:Z33").Value = Path2
    End If


End Sub

 

[6StringJazzer]

What is the error message and what line of code is highlighted when the error occurs?

You said "the error takes me to various other bits of codes" but how is this possible? A runtime error causes execution to halt, in the absence of an error handler, which is absent in this code.

 

[sassriverrat]

So usually I get a "Method "intersect of object'_Global' Failed" and it highlights the timestamp used in another cell (see code below)- line in red.

This code is designed to insert a timestamp in F4 based at least one of two other cells being populated- one is an "override" to fill F4 with its contents no matter what and the other is designed just to tell F4 that this sheet is being used and to stamp F4. Thanks

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)If ActiveSheet.Name = "Developer" _
    Or ActiveSheet.Name = "Notes" _
    Or ActiveSheet.Name = "Ports" _
    Or ActiveSheet.Name = "Voyage Specifics" _
    Then Exit Sub
With Application
    .EnableEvents = False
    .ScreenUpdating = False
If Not Intersect(Target, Union(Range("R5"), Range("W25"))) Is Nothing Then
    If Cells(25, 23) <> "" Then
        Cells(4, 6) = Cells(25, 23).Value
        Cells(4, 6).NumberFormat = "dd-mmm-yy"
    ElseIf Cells(5, 18) <> "" And Cells(25, 23) = "" Then
        Cells(4, 6) = Date
        Cells(4, 6).NumberFormat = "dd-mmm-yy"
    ElseIf Cells(5, 18) = "" And Cells(6, 23) = "" Then
        Cells(4, 6) = "No Data Input"
    End If
End If


If ActiveSheet.Range("Z20") = "Yes" Then
   Cells(9, 18) = "Exact"
End If


    .EnableEvents = True
    .ScreenUpdating = True


End With
End Sub

 

[Rick Rothstein]

Off the top of my head, I am not sure why you are getting that error, but I am also wondering why you did use a more direct test. The line of code that you are erroring on should be able to be replaced by this equivalent line of code...

[table="width: 500"]
[tr]
	[td]If Target.Address(0, 0) = "R5" Or Target.Address(0, 0) = "W25" Then[/td]
[/tr]
[/table]

 

[sassriverrat]

Thanks Rick-

I'll let you know in a few hours when it runs if it all works as it should. Thanks again

 

[rlv01]

Another possible way to test

        Select Case Target.Address
        Case "$R$5", "$W$25"
            If Cells(25, 23) <> "" Then
                Cells(4, 6) = Cells(25, 23).Value
                Cells(4, 6).NumberFormat = "dd-mmm-yy"
            ElseIf Cells(5, 18) <> "" And Cells(25, 23) = "" Then
                Cells(4, 6) = Date
                Cells(4, 6).NumberFormat = "dd-mmm-yy"
            ElseIf Cells(5, 18) = "" And Cells(6, 23) = "" Then
                Cells(4, 6) = "No Data Input"
            End If
        End Select

 

[sassriverrat]

@Rick Rothstein

I've got it! So I should have realized this before.

The ETA calc is running it's numbers based on the coding (formula) and since it's not a function in the cell itself, I would imagine excel is running the code and interpreting the time (1230) as a serial, not as the time. So is there a way to run the function within vba? I presume I could write the code with the function spelled in it.

See red below. Remember that militarytotime coding (also below)

Sub ETA_CALC1()


Dim Path1 As Date
Dim Path2 As Date
Dim Path3 As Double
Dim Path4 As Double
Dim Path5 As Double
Dim path6 As Double
Dim Path7 As Date
Dim Path8 As Date
Dim DTG As Double
Dim resp As Integer
'This says that the Distance to go should be in D10 but if we want to use a different mileage than what today's report had list, _
(D10 is on the list), we can input into S32 to use our own distance


'This piece ensures "ETA Arrival ZD" is ready
Worksheets("Developer").Range("F3").FormulaR1C1 = "=IF(AND((Notes!R[10]C[6]+Notes!R[10]C[7])<(('Voyage Specifics'!R[6]C[-3]+'Voyage Specifics'!R[7]C[-3])),(Notes!R[11]C[6]+Notes!R[11]C[7])>(('Voyage Specifics'!R[6]C[-3]+'Voyage Specifics'!R[7]C[-3]))),""Yes"",""No"")"


If ActiveSheet.Range("S29").Value = "" Then
    DTG = ActiveSheet.Range("D10").Value
    Else: DTG = ActiveSheet.Range("S29").Value
End If


Path1 = ActiveSheet.Range("R28").Value
Path2 = ActiveSheet.Range("T28").Value
Path3 = DTG




Path5 = Sheets("Developer").Range("G2").Value
path6 = ActiveSheet.Range("C5").Value
Path7 = ActiveSheet.Range("F4").Value
Path8 = ActiveSheet.Range("D4").Value


Path4 = (Path3 / (((Path2 + Path1 + (TimeSerial(Path5, 0, 0))) - (Path7 + Path8 + (TimeSerial(path6, 0, 0)))) * 24))
'So here with path 1 - Putting a time in with a colon works perfectly- putting a time in as a military time (eg 1230) spits back a "0.00" answer, which
I presume is excel reading the value incorrectly. Can I adjust part of the code and replace Path1 with MilitaryToTime(Path1) ?




resp = MsgBox("Based on your desired Arrival Time/Date and your mileage input, your speed required to make your ETA is: " & Round(Path4, 1) & " knots" & vbCrLf & vbCrLf & "Would you like to use this ETA for Today's Report?", vbYesNo)
If resp = vbYes Then
    ActiveSheet.Range("W33").Value = Format(Path1, "hh:mm;@")
    ActiveSheet.Range("Y33:Z33").Value = Path2
    End If
End Sub

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