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
Any help much appreciated
Paul
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 SubAny help much appreciated
Paul
