Msgbox Calculator

[sassriverrat]

Good Morning,

I've started some code below where I'd like the data from a few cells (times and dates) to be computed. The answer should popup in a msgbox and then it should ask me if this data should be put into Cells "W33" and "Y33"

I've got some of this code wrong and it's giving me errors on the path4. Thanks for the help.

Sub ETA_CALC1()

Dim Path1 As String
Dim Path2 As String
Dim Path3 As String
Dim Path4 As String
Dim Path5 As String
Dim Path6 As String
Dim Path7 As String
Dim Path8 As String




Path1 = ActiveSheet.Range("R28").Value
Path2 = ActiveSheet.Range("T28").Value
Path3 = ActiveSheet.Range("S29").Value
Path4 = Value
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 + (Time(Path5, 0, 0))) - (Path7 + Path8 + (Time(Path6, 0, 0)))) * 24))




MsgBox ("Based on your desired Arrival Time and Date and your mileage input, your speed required to make your ETA is:" & vbCrLf & Path4)










End Sub

 

[KolGuyXcel]

Try this after "deleting" the line:

path4 = value

Also define path4 as double!

Sub ETA_CALC1()

Dim Path1 As String
Dim Path2 As String
Dim Path3 As String
Dim Path4 As double
Dim Path5 As String
Dim Path6 As String
Dim Path7 As String
Dim Path8 As String




Path1 = ActiveSheet.Range("R28").Value
Path2 = ActiveSheet.Range("T28").Value
Path3 = ActiveSheet.Range("S29").Value
'Path4 = Value
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 + (Time(Path5, 0, 0))) - (Path7 + Path8 + (Time(Path6, 0, 0)))) * 24))




MsgBox ("Based on your desired Arrival Time and Date and your mileage input, your speed required to make your ETA is:" & vbCrLf & Path4)










End Sub

 

[sassriverrat]

so I changed it as you said, to a double, and deleted the path4 = value

I still get an error, '13, and type mismatch and it highlights the Path4 = my whole formula line.

Ideas?

 

[rlv01]

The runtime Error 13 is because you are using the VBA Time function in an illegal way

Time(Path5, 0, 0)

You have to use the Timeserial function instead.

TimeSerial(Path5, 0, 0)

https://docs.microsoft.com/en-us/of...rence/user-interface-help/timeserial-function

 

[sassriverrat]

Please see code below. Still highlighting the Path4 = my formula
I've tried adding Path4 = value just to be sure but that didn't help

Sub ETA_CALC1()


Dim Path1 As String
Dim Path2 As String
Dim Path3 As String
Dim Path4 As Double
Dim Path5 As String
Dim Path6 As String
Dim Path7 As String
Dim Path8 As String




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


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))




MsgBox ("Based on your desired Arrival Time and Date and your mileage input, your speed required to make your ETA is:" & vbCrLf & Path4)










End Sub

 

[rlv01]

Post some sample data for Path1,2,3,5,6,7,8 and then tell us what the Path4 result should be for that sample data.

 

[sassriverrat]

Thanks for looking into this @rlv01!

Path1 = a time in the future, say 12:00
Path2 = a date in the future, say 11/12/2018
Path3 = a number (a mileage), say 50
Path5 = a number, say 5 (which becomes formatted as an hour using the timeserial function) (technically a zone description adjustment for UTC)
Path6 = a number, say 6 (which becomes formatted as an hour using the timeserial function) (technically a zone description adjustment for UTC)
Path7 = a date (today), say 11/11/2018
Path8 = a time (today), say 12:00

Simple concept, tad more complicated execution- Time x Speed = Distance Today's time/date are givens as well as my zone description (say East Coast time) and I want to know how fast I have to go to get to somehwere else (say California in Pacific Time) by a given (Path1 and 2) time and date

 

[rlv01]

Try this.

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
    '
    Path1 = ActiveSheet.Range("R28").Value
    Path2 = ActiveSheet.Range("T28").Value
    Path3 = ActiveSheet.Range("S29").Value
    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))

    MsgBox ("Based on your desired Arrival Time, Date, and mileage input, your speed required to make your ETA is: " & Round(Path4, 1) & " mph")
End Sub

 

[sassriverrat]

Sub ETA_CALC2()


Dim Path1 As Date 'today's date
Dim Path2 As Double 'today's time
Dim Path3 As Double 'today's Zone Description
Dim Path4 As Double 'Answer
Dim Path5 As Double 'Distance to go
Dim Path6 As Double 'speed
Dim Path7 As Double 'arrival zone description
Dim DTG As Double


'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


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


Path1 = ActiveSheet.Range("F4").Value
Path2 = ActiveSheet.Range("D4").Value
Path3 = ActiveSheet.Range("C5").Value
Path5 = DTG
Path6 = ActiveSheet.Range("S31").Value
Path7 = Sheets("Developer").Range("G2").Value




Path4 = ((Path1 + (Path2 + Path3)) + ((Path5 / Path6) + TimeSerial(Path7, 0, 0))) 'So this should return an excel number (time and date combined)




MsgBox ("Based on your expected speed and your mileage input, your ETA, corrected for arrival local time, is " & TimeValue(Path4) _
& " " & "/" & " " & DateValue(Path4))
'This is kind of where I get stuck- I know I haven't used the DateValue correctly but I'm not exactly sure how I would write this
End Sub

 

[rlv01]

MsgBox "Based on your expected speed and your mileage input, your ETA, corrected for arrival local time, is " & CDate(Path4)