Sum the total working hours when the Qty Completed become value except 0 automatically

yoon

New Member
Joined
Jul 25, 2023
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hye everyone, good morning. I need help from expert asap with my problem. so i attached my coding and picture as references result that i want.

Private Sub CommandButton3_Click()
Dim ws As Worksheet
Dim lastRow As Long
Dim productName As String
Dim processNumber As String
Dim totalQty As Double
Dim workingHours As Double
Dim totalHours As Double
Dim foundRow As Range

' Get the selected worksheet from the combobox
Set ws = ThisWorkbook.Sheets(Me.ComboBox1.Value)

' Find the last used row in the worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

' Retrieve values from textboxes
productName = Me.TextBox2.Value
processNumber = Me.TextBox4.Value
totalQty = CDbl(Me.TextBox7.Value)
workingHours = CDbl(Me.TextBox6.Value)

' Find the previous row with the same product and process
Set foundRow = ws.Range("B11:B" & lastRow).Find(productName & processNumber, LookIn:=xlValues, LookAt:=xlWhole)

' Calculate total hours and update columns
If totalQty = 0 Then
totalHours = 0
Else
If Not foundRow Is Nothing Then
totalHours = ws.Cells(foundRow.Row, "G").Value ' Use the existing total hours
Else
totalHours = 0
End If

If totalQty = 1 Then
totalHours = totalHours + workingHours ' Sum working hours when total qty is 1
End If
End If

' Add data to the worksheet
ws.Cells(lastRow, "A").Value = TextBox1.Value 'day
ws.Cells(lastRow, "B").Value = productName
ws.Cells(lastRow, "C").Value = TextBox3.Value 'Product Assign
ws.Cells(lastRow, "D").Value = processNumber
ws.Cells(lastRow, "E").Value = TextBox5.Value
ws.Cells(lastRow, "F").Value = workingHours
ws.Cells(lastRow, "G").Value = totalHours
ws.Cells(lastRow, "H").Value = ws.Cells(lastRow, "E").Value ' Total Qty Completed

' Update total quantity in the previous row if applicable
If Not foundRow Is Nothing Then
ws.Cells(foundRow.Row, "E").Value = ws.Cells(foundRow.Row, "E").Value + totalQty
ws.Cells(foundRow.Row, "G").Value = totalHours
ws.Cells(foundRow.Row, "H").Value = ws.Cells(foundRow.Row, "E").Value ' Total Qty Completed
End If

' Clear textboxes
Me.TextBox2.Value = ""
Me.TextBox4.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""

' Inform user
MsgBox "Data submitted successfully!", vbInformation
End Sub

Private Sub userform_initialize()


Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ComboBox1.AddItem ws.Name
End If
Next ws

End Sub


So, above is my current coding and i encountered some problem that need to solve asap!. so if you can see form my coding above, i want to sum the working hours when the qty completed become 1 or any values except 0 and automatically put the value in column G based on same product name and process number. so i want the result become like the image that attached below:
as you can see form the sheet, the working hours will be sum when the qty become 1 in next day. and when the third day it completed 2 qty and the working hours will auto paste the value in column G as the value qty not 0. so can you help me correct my coding above
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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