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
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