VBA to fun formula in code

Paul15

New Member
Joined
Jun 25, 2020
Messages
37
Office Version
  1. 2019
Platform
  1. Windows
Dear All,

i have a working Formula thanks to this site which works and had put this on my spreadsheet and dragged it down multiple lines

formula is =IF(AJ3>AX1,"Yes","No") where AX1 is 01:00 - 1 hour

the formula calculates where AJ3 is greater than 01:00, 1hour and puts "yes" in column AX3. it works fine, however I use a data input sheet for every line of data entered into the sheet. The form will copy to the next available blank line. Therefore I cannot drag the formula down the column as it pushes each new data line via the input form down the spreadsheet.
I did this last night without thinking, dragging the formula down 10000 lines. Funnily enough my users were complaining the coded input form was not working. Well it was working only the data was going from line 10000 down and couldn't be seen. Then I remembered you cannot put or drag formulas into the spreadsheet as it stops the VBA doing what you expect. Lesson remembered.

Therefore I am now looking to code this concept. My code already calculates the difference from a start time to end time and gives a total. I now want to code, where if total time on any line in column AJ is > than 01:00, true, then "yes" is set in column AX for each newly entered line of data through the input form. If not true, then "No" is set

I use this "Yes" in AX to filter on a query

My time calculation code

VBA Code:
Private Sub Update_TotalTime()

    If Me.Time1.Value = "" Or Me.timepcp2.Value = "" Then
        Me.TotalTime.Value = "00:00"
    ElseIf Not IsDate(Me.Time1.Value) Then
        MsgBox "Invalid 'Start' time", vbExclamation, "Invalid Entry"
        Me.TotalTime.Value = "00:00"
    ElseIf Not IsDate(Me.timepcp2.Value) Then
        MsgBox "Invalid 'End' time", vbExclamation, "Invalid Entry"
        Me.TotalTime.Value = "00:00"
    ElseIf TimeValue(timepcp2.Value) < TimeValue(Time1.Value) Then
        MsgBox "'Time of inital stop' time occurs after 'Time Clear of PCP' time. If Time Clear of PCP is the next day then please amend Total ", vbExclamation, "Invalid Entries"
        Me.TotalTime.Value = "00:00"
    Else
        Me.TotalTime.Value = Format(TimeValue(Me.timepcp2.Value) - TimeValue(Me.Time1.Value), "hh:mm")
    End If
    
End Sub

Any help much appreciated

Paul
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,258
Messages
5,836,281
Members
430,414
Latest member
ayla

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
Top