Help me find error in this coding

[yoon]

| day | product name | product assign | process number | estimate time | working hours | total hours completed | total qty completed |
|------|--------------|----------------|----------------|---------------|--------------|----------------------|--------------------|
| 1 | ABC | - | 25 | 200 | 12 | 0 | 0 |
| 2 | ABC | - | 25 | 200 | 8 | 20 | 1 |
| 3 | ABC | - | 25 | 200 | 10 | 10 | 2 |
| 1 | ABC | - | 15 | 100 | 12 | 12 | 1 |
| 2 | DEF | - | 16 | 300 | 8 | 0 | 0 |
| 3 | DEF | - | 16 | 300 | 10 | 10 | 2 |


Above is the result which i will get when i submit the data in my userform.
Below is my coding:

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

' 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
estimateTime = CDbl(Me.TextBox5.Value)
workingHours = CDbl(Me.TextBox6.Value)
totalQty = CDbl(Me.TextBox7.Value)

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

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

' Check if totalQty is 1 to accumulate working hours
If totalQty = 1 Then
totalWorkingHours = totalWorkingHours + workingHours
End If

ws.Cells(lastRow, "G").Value = totalWorkingHours
Else
ws.Cells(lastRow, "G").Value = 0 ' Clear total working hours when quantity is 0
End If

' Add data to the worksheet in a table-like format
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 = estimateTime
ws.Cells(lastRow, "F").Value = workingHours
ws.Cells(lastRow, "H").Value = totalQty ' Total Qty Completed

' Clear textboxes
Me.TextBox2.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.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 ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ComboBox1.AddItem ws.Name
End If
Next ws
End Sub

From coding above, i encountered error and the error we can see from the image i attached below. i hope you guys can help me correct my coding and get like the result above.

 

[rondeondo]

Hi,
I'm guessing that your issue is the blanks in "product assign" column, since you haven't actually stated what the problem is.
There is no point in your code where you collect this data from your form
I'd guess it should be in this bit of your code.

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

Hope this helps

 

[yoon]

thank you for reaching out, but the column product assign indeed become "-". my problem is the total hours completed what result i want and what i get based on coding i used is different. so i need the coding to be corrected and get the result i want above

 

[rondeondo]

I think you need to describe the actual result you want and any logic behind it to help identify where the problem may be. Pasting in code saying this isn't working how I want does require a description of what working code would do and how you want it to work

 

[yoon]

okay currently this is my coding below:
Private Sub CommandButton3_Click()
Dim ws As Worksheet
Dim lastRow As Long
Dim productName As String
Dim processNumber As String
Dim estimateTime As Double
Dim workingHours As Double
Dim totalQty As Double

' 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
estimateTime = CDbl(Me.TextBox5.Value)
workingHours = CDbl(Me.TextBox6.Value)
totalQty = CDbl(Me.TextBox7.Value)

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

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

' Check if totalQty is 0, set total hours and total qty to 0
If totalQty = 0 Then
totalWorkingHours = 0
End If

' Check if totalQty is 1 and the previous row's totalQty was 1, then accumulate working hours
If totalQty = 1 And (foundRow Is Nothing Or ws.Cells(lastRow - 1, "H").Value = 1) Then
totalWorkingHours = totalWorkingHours + workingHours
End If

' Update total hours completed column
ws.Cells(lastRow, "G").Value = totalWorkingHours

' Update other columns
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 = estimateTime
ws.Cells(lastRow, "F").Value = workingHours
ws.Cells(lastRow, "H").Value = totalQty ' Total Qty Completed

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


Private Sub UserForm_Initialize()
Dim ws As Worksheet

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


then, I attached two image. for the first image is the result when i fill the data in userform by using coding above. then the second image is the result that I want. Can you help me correct my current coding to make the result upcoming as second image.

 

[yoon]

i add third image which is the original daily report before i want to make it automatic userform that fill value in column total hours completed.

 

[Peter_SSs]

@yoon

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.

Also, please review the Forum Rules particularly #14 in relation to using all caps in thread titles. I have changed the couple you created recently.