Running Pace formula help

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
Hi good afternoon, hope you can help me please, i have a userform with textbox1 is the Time, Textbox2 is the miles ran and textbox3 will be the pace, in CommandButton1 this is the button to work out the calculation for the pace, i have put in the formula below but it does not work when i put in decimal points in on the time or miles for example, so if i did 5 miles in 10 minutes then it works but if i put 3.11 miles in 27:32 i get an error. Please help, do i need to change the value in textbox1 (time)_ to HH:MM? if so how do i do this? please advise
VBA Code:
Private Sub CommandButton1_Click()
    TextBox3.Value = (TextBox1.Value / TextBox2.Value)
End Sub

EDIT:
i think i need to do this as well but unsure how Time is in hours : minutes : seconds, Distance is in miles, and Pace is in minutes/mile matching the unit Distance is in.
Pace ≠ Speed

Pace is minutes per 1 unit of distance, while Speed is distance per time unit.

Though they are not equal you can convert between them using the distance form of the top formula:


Problem: How fast is a 8:30 pace
Known: Pace = 8.5 minutes for 1 mile
Time = 1 hour or 60 minutes
Needed: Distance = Time / Pace

Calculations: Distance = 60 minutes/8.5 minutes
= 7.06 miles / hour

So, an 8:30 pace = 7.06 miles/hour speed
 
Last edited by a moderator:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi i have done the hh:mm:ss i think now in my other textboxes and the duration i have changed to number format i think this is correct but still struggling with the original code as it doesnt like the decimal points
VBA Code:
Private Sub CommandButton1_Click()
    TextBox3.Value = (TextBox1.Value / TextBox2.Value)
End Sub

Private Sub TextBox1_Change()
TextBox1.Value = Format(TextBox1, "hh:mm:ss")
End Sub

Private Sub TextBox2_AfterUpdate()
    If Me.TextBox2 <> "" Then
        Me.TextBox2 = Format(Me.TextBox2 / 100, "#,##0.00")
    End If
End Sub

Private Sub TextBox3_Change()
TextBox3.Value = Format(TextBox3, "hh:mm:ss")
End Sub
 
Upvote 0
hi i sort of got it working now (not properly) with the coding below, but the pace is going over the 60 seconds for example i can get a pace of 8.78 which has gone over the 60 seconds so it should be 9.18. how can i do this please.
VBA Code:
Private Sub CommandButton1_Click()
    TextBox3.Value = (TextBox1.Value / TextBox2.Value)
End Sub

Private Sub TextBox1__Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox1.Value = Format(Me.TextBox1.Value, "hh:mm:ss")
End Sub

Private Sub TextBox2__AfterUpdate()
With Me.TextBox2
If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0.00")

End With
End Sub

Private Sub UserForm_Initialize()
With Me.TextBox2
    .Value = 0
    .Value = Format(.Value, "0.00")
End With
End Sub
Private Sub TextBox3__Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox3.Value = Format(Me.TextBox3.Value, "hh:mm:ss")
End Sub
 
Upvote 0
Hi hope you can help me please? I hope it all makes sense? Basically what I want is textbox1 (time( divide by textbox2 (distance) and total of minutes in textbox2 (pace) but at moment the the equation is going over the 60 seconds for example 8.89 secs but as its gone over the 60 it should be 9.29 secs. Hope you can help
 
Upvote 0
Hi hope you can help me please? I hope it all makes sense? Basically what I want is textbox1 (time( divide by textbox2 (distance) and total of minutes in textbox2 (pace) but at moment the the equation is going over the 60 seconds for example 8.89 secs but as its gone over the 60 it should be 9.29 secs. Hope you can help
Hi how do i put this in correctly? Do you think this would work?

VBA Code:
Private Sub CommandButton1_Click()
    TextBox3.Value = TextBox1.Value / (TextBox2.Value*24)
End Sub
 
Upvote 0
Hi how do i put this in correctly? Do you think this would work?

VBA Code:
Private Sub CommandButton1_Click()
TextBox3.Value = TextBox1.Value / (TextBox2.Value*24)
End Sub
 
Upvote 0
Hi i have updated my code now to the below after many hours researching on the internet, but i am getting one line turning red please cab you advise?
This is the line where it is all red
Code:
value = CSng((TextBox2.Text * 60) / TextBox3.Text))

This is the whole code:
VBA Code:
Private Sub CommandButton1_Click()
Dim sec As Single
Dim min As Integer
Dim value As Single

value = CSng((TextBox2.Text * 60) / TextBox3.Text))
min = Int(value)
sec = (value Mod min) * 60

TextBox1.Text = Str$(min) & ":" & Str$(sec)
End Sub

Private Sub CommandButton2_Click()
Dim x As Integer
For x = 1 To 3
   Select Case x

     Case Else
         Me.Controls("textbox" & x).value = ""
   End Select
Next x
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub





Private Sub TextBox1_Change()
Dim inMin As Integer
Dim inSec As Integer

inMin = Left$(TextBox1.Text, 1)
inSec = Right$(TextBox1.Text, 1)
End Sub

    
Private Sub TextBox3_Change()
Dim inMin As Integer
Dim inSec As Integer

inMin = Left$(TextBox3.Text, 3)
inSec = Right$(TextBox3.Text, 3)
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,970
Messages
6,175,726
Members
452,667
Latest member
vanessavalentino83

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