Need help (If Statement)

[hhalyami43]

Greetings,

basically I created this code for maintenance team to record data of machine breakdown
I'm trying run the following code but it keeps give me error msg (run time error '13' mistype) when i run it


first I want to add start time and end time will blank until the machine got repaired then update end time and applying the following formula:

duration(I)= end time(H) - start time(G)

so I need your help




Thanks in advance!

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

 sh.Range("G" & last_Row + 1).Value = TextBox4.Value & " " & ComboBox4.Value         'start in format (mm/dd/yyyy hh:mm)
 sh.Range("H" & last_Row + 1).Value = TextBox5.Value & " " & ComboBox5.Value         'end in format (mm/dd/yyyy hh:mm)

If sh.Range("H" & last_Row + 1).Value < sh.Range("G" & last_Row + 1).Value Then      'Error msg when negative value
  MsgBox "End breakdown must be bigger than start breakdown", vbCritical
Exit Sub
End If

If sh.Range("H" & last_Row + 1).Value = "" Then          
 sh.Range("I" & last_Row + 1).Value = ""
Else
 sh.Range("I" & last_Row + 1).Value = sh.Range("H" & last_Row + 1).Value - sh.Range("G" & last_Row + 1).Value
End If

[SamT]

First: Clarify what you're doing
Get Start and End DateTimes
Record such in G ad H
Record DateTime remaining in I
(Check entered values for End > start)

Now, let's clean up your code

Dim sh As Worksheet, StartTime As Date, EndTime As Date, NextRow As long
Set sh = ThisWorkbook.Sheets("Sheet1")
NextRow = Last_Row + 1

If len(TextBox4) * Len(ComboBox4) = 0 then 'Either start Date or start Time is empty
    MsgBox "Gotta have a Start Time"
    Exit Sub
End If

startTime = CDate(TextBox4 & " " & ComboBox4)
sh.Rows(NextRow).Cells("G") = Format(StartTime, "mm/dd/yyyy hh:mm")

If Len(TextBox5) + Len(ComboBox5) > 0 And Len(TextBox5) * Len(ComboBox5) = 0 Then 'only one is empty
       MsgBox "Gotta fill in neither or both endtime boxes."
       Exit Sub
End If

If len(TextBox5) + Len(ComboBox5) = 0 then Exit Sub 'No end Time, all done

EndTime = CDate(TextBox5 & " " & ComboBox5)
If EndTime =< Startime Then
       msgbox "end time gotta be after start time"
       Exit Sub
End If

With sh.Rows(NextRow)
       .Cells("H") = Format(EndTime, "mm/dd/yyyy hh:mm")
       .Cells("I") = Format(EndTime - StartTime, "0m Months, dd Days, and 0h Hours, 0m Minutes")
       'That simple line won't work for the remaining Time. You will need to check the number of months,
       'and days remaining, then decide what you want displayed. 
       'Use a separate Function.
       '.Cells("I") = Customformattingfunction(StartTime, EndTime)
End With
End Sub

[hhalyami43]

Hello SamT,

i got another error check attchment please

Capture2.jpg

Private Sub CommandButton1_Click()

Dim sh As Worksheet, StartTime As Date, EndTime As Date, NextRow As Long, last_Row As Long

Set sh = ThisWorkbook.Sheets("outputs")

last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

NextRow = last_Row + 1

If Len(TextBox4) * Len(ComboBox4) = 0 Then 'Either start Date or start Time is empty

    MsgBox "Gotta have a Start Time"

    Exit Sub

End If

StartTime = CDate(TextBox4 & " " & ComboBox4)

sh.Rows(NextRow).Cells("G") = Format(StartTime, "mm/dd/yyyy hh:mm")

If Len(TextBox5) + Len(ComboBox5) > 0 And Len(TextBox5) * Len(ComboBox5) = 0 Then 'only one is empty

       MsgBox "Gotta fill in neither or both endtime boxes."

       Exit Sub

End If

If Len(TextBox5) + Len(ComboBox5) = 0 Then Exit Sub 'No end Time, all done

EndTime = CDate(TextBox5 & " " & ComboBox5)

If EndTime <= Startime Then

       MsgBox "end time gotta be after start time"

       Exit Sub

End If

With sh.Rows(NextRow)

       .Cells("H") = Format(EndTime, "mm/dd/yyyy hh:mm")

       .Cells("I") = Format(EndTime - StartTime, "0m Months, dd Days, and 0h Hours, 0m Minutes")

       'That simple line won't work for the remaining Time. You will need to check the number of months,

       'and days remaining, then decide what you want displayed.

       'Use a separate Function.

       '.Cells("I") = Customformattingfunction(StartTime, EndTime)

End With

    Call Refresh_Data
    
    TextBox1.Value = Sheet18.Range("A1").CurrentRegion.Rows.Count

End Sub

[Paul_Hossler]

Thee's no attachment to test, but try ...

sh.Cells(NextRow, 7).Value = Format(StartTime, "mm/dd/yyyy hh:mm")

[SamT]

What Paul said
Try replacing G,H,& I with column numbers