PDA

View Full Version : Need help (If Statement)



hhalyami43
04-05-2020, 02:18 AM
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
04-05-2020, 03:03 AM
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
04-05-2020, 04:05 AM
Hello SamT,

i got another error check attchment please

26280

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
04-05-2020, 05:55 AM
Thee's no attachment to test, but try ...





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

SamT
04-05-2020, 11:01 AM
What Paul said
Try replacing G,H,& I with column numbers