Userform for sum the same values by the same data in previous column

Y

yoon

New Member
Joined
Jul 25, 2023
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hye everyone, i am new to this forum and need all of your help asap TT. currently i am student and do my internship for one company and need to improvise the data given by the company. and I already have my userform but i need to sum the total hours and total quantity by the same process number and product name. can you all help me because currently i have no idea and blank already TT.
 

Attachments

  • Screenshot 2023-07-26 083752.png
    Screenshot 2023-07-26 083752.png
    48.1 KB · Views: 8
Sort by date Sort by votes
okay I already try it thank you. but actually my question is I want the data is from userform and when I submit it will display on the sheet. then, if the second product name is same and the process number is the same I put in the userform, it will auto sum on the sheet with no duplicate data. but if the product name still same but the process number is different I want it to show in the sheet for next row. If you not understand i will attach new photo.
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Can you show me where in your original post it says that you are going to pass the data from the userform to the sheet?
 
Upvote 0
yoon said:
my question is I want the data is from userform and when I submit it will display on the sheet. then, if the second product name is same and the process number is the same I put in the userform, it will auto sum on the sheet with no duplicate data. but if the product name still same but the process number is different I want it to show in the sheet for next row.
Click to expand...
👆
According to the new information.
We start again...

Create your user form with 4 textboxes.

Put the following code in commandbutton1:
VBA Code: 
Private Sub CommandButton1_Click()
  Dim i As Long, lr
  Dim Foundit As Boolean

  If TextBox1.Value = "" Then
    MsgBox "Enter Product"
    TextBox1.SetFocus
    Exit Sub
  End If
  If TextBox2.Value = "" Then
    MsgBox "Enter Process"
    TextBox2.SetFocus
    Exit Sub
  End If
  If TextBox3.Value = "" Or Not IsNumeric(TextBox3.Value) Then
    MsgBox "Enter hours"
    TextBox3.SetFocus
    Exit Sub
  End If
  If TextBox4.Value = "" Or Not IsNumeric(TextBox4.Value) Then
    MsgBox "Enter qty"
    TextBox4.SetFocus
    Exit Sub
  End If
  
  lr = Range("A" & Rows.Count).End(3).Row
  For i = 1 To lr
    If Range("A" & i).Value = TextBox1.Value And Range("B" & i).Value = TextBox2.Value Then
      Foundit = True
      Exit For
    End If
  Next
  
  If Foundit = False Then
    i = lr + 1
    Range("A" & i).Value = TextBox1.Value
    Range("B" & i).Value = TextBox2.Value
  End If
  Range("C" & i).Value = Range("C" & i).Value + TextBox3.Value
  Range("D" & i).Value = Range("D" & i).Value + TextBox4.Value
    
  TextBox1.Value = ""
  TextBox2.Value = ""
  TextBox3.Value = ""
  TextBox4.Value = ""
End Sub

So you capture the 4 data and press the button.

I attach the file again with the new form and the new code.

varios 25jul2023 userform sum.xlsm

drive.google.com drive.google.com
Note: You must download the file to your computer and then you can edit it.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​



"Si no sabes por dónde empezar, empieza por un café."
"If you don't know where to start, start with a coffee."
☕​
 
Upvote 0
DanteAmor said:
👆
According to the new information.
We start again...

Create your user form with 4 textboxes.

Put the following code in commandbutton1:
VBA Code: 
Private Sub CommandButton1_Click()
  Dim i As Long, lr
  Dim Foundit As Boolean

  If TextBox1.Value = "" Then
    MsgBox "Enter Product"
    TextBox1.SetFocus
    Exit Sub
  End If
  If TextBox2.Value = "" Then
    MsgBox "Enter Process"
    TextBox2.SetFocus
    Exit Sub
  End If
  If TextBox3.Value = "" Or Not IsNumeric(TextBox3.Value) Then
    MsgBox "Enter hours"
    TextBox3.SetFocus
    Exit Sub
  End If
  If TextBox4.Value = "" Or Not IsNumeric(TextBox4.Value) Then
    MsgBox "Enter qty"
    TextBox4.SetFocus
    Exit Sub
  End If
 
  lr = Range("A" & Rows.Count).End(3).Row
  For i = 1 To lr
    If Range("A" & i).Value = TextBox1.Value And Range("B" & i).Value = TextBox2.Value Then
      Foundit = True
      Exit For
    End If
  Next
 
  If Foundit = False Then
    i = lr + 1
    Range("A" & i).Value = TextBox1.Value
    Range("B" & i).Value = TextBox2.Value
  End If
  Range("C" & i).Value = Range("C" & i).Value + TextBox3.Value
  Range("D" & i).Value = Range("D" & i).Value + TextBox4.Value
   
  TextBox1.Value = ""
  TextBox2.Value = ""
  TextBox3.Value = ""
  TextBox4.Value = ""
End Sub

So you capture the 4 data and press the button.

I attach the file again with the new form and the new code.

varios 25jul2023 userform sum.xlsm

drive.google.com drive.google.com
Note: You must download the file to your computer and then you can edit it.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​



"Si no sabes por dónde empezar, empieza por un café."
"If you don't know where to start, start with a coffee."
☕​
Click to expand...
good morning Mr. Dante, thank you reach me at late night:) I will try again the new code and if I encountered problem I will reach you asap.
 
Upvote 0
yoon said:
good morning Mr. Dante, thank you reach me at late night:) I will try again the new code and if I encountered problem I will reach you asap.
Click to expand...
hello Mr. Dante, how can we make the value of completed hour and completed qty is still sum even it different? Because for your formula, the total hour and total qty must be same with the column before, then it will be sum. The picture I attached is how can I make the p4 and process number 35 is still sum the total qty and hour even it different value?
 

Attachments

  • Screenshot 2023-07-27 083415.png
    Screenshot 2023-07-27 083415.png
    30.7 KB · Views: 2
Upvote 0
Explanation: All data in the textboxes is stored as text. On the sheet in column B you have numbers, so we are comparing numbers against text, that's why it doesn't find it.

Here the corrected code, try again.
VBA Code: 
Private Sub CommandButton1_Click()
  Dim i As Long, lr
  Dim Foundit As Boolean
  Dim txt1 As Variant, txt2 As Variant

  txt1 = IIf(IsNumeric(TextBox1.Value), Val(TextBox1.Value), TextBox1.Value)
  txt2 = IIf(IsNumeric(TextBox2.Value), Val(TextBox2.Value), TextBox2.Value)
  If TextBox1.Value = "" Then
    MsgBox "Enter Product"
    TextBox1.SetFocus
    Exit Sub
  End If
  If TextBox2.Value = "" Then
    MsgBox "Enter Process"
    TextBox2.SetFocus
    Exit Sub
  End If
  If TextBox3.Value = "" Or Not IsNumeric(TextBox3.Value) Then
    MsgBox "Enter hours"
    TextBox3.SetFocus
    Exit Sub
  End If
  If TextBox4.Value = "" Or Not IsNumeric(TextBox4.Value) Then
    MsgBox "Enter qty"
    TextBox4.SetFocus
    Exit Sub
  End If
  
  lr = Range("A" & Rows.Count).End(3).Row
  For i = 1 To lr
    If Range("A" & i).Value = txt1 And Range("B" & i).Value = txt2 Then
      Foundit = True
      Exit For
    End If
  Next
  
  If Foundit = False Then
    i = lr + 1
    Range("A" & i).Value = TextBox1.Value
    Range("B" & i).Value = TextBox2.Value
  End If
  Range("C" & i).Value = Range("C" & i).Value + TextBox3.Value
  Range("D" & i).Value = Range("D" & i).Value + TextBox4.Value
    
  TextBox1.Value = ""
  TextBox2.Value = ""
  TextBox3.Value = ""
  TextBox4.Value = ""
End Sub
 
Upvote 0
DanteAmor said:
Explanation: All data in the textboxes is stored as text. On the sheet in column B you have numbers, so we are comparing numbers against text, that's why it doesn't find it.

Here the corrected code, try again.
VBA Code: 
Private Sub CommandButton1_Click()
  Dim i As Long, lr
  Dim Foundit As Boolean
  Dim txt1 As Variant, txt2 As Variant

  txt1 = IIf(IsNumeric(TextBox1.Value), Val(TextBox1.Value), TextBox1.Value)
  txt2 = IIf(IsNumeric(TextBox2.Value), Val(TextBox2.Value), TextBox2.Value)
  If TextBox1.Value = "" Then
    MsgBox "Enter Product"
    TextBox1.SetFocus
    Exit Sub
  End If
  If TextBox2.Value = "" Then
    MsgBox "Enter Process"
    TextBox2.SetFocus
    Exit Sub
  End If
  If TextBox3.Value = "" Or Not IsNumeric(TextBox3.Value) Then
    MsgBox "Enter hours"
    TextBox3.SetFocus
    Exit Sub
  End If
  If TextBox4.Value = "" Or Not IsNumeric(TextBox4.Value) Then
    MsgBox "Enter qty"
    TextBox4.SetFocus
    Exit Sub
  End If
 
  lr = Range("A" & Rows.Count).End(3).Row
  For i = 1 To lr
    If Range("A" & i).Value = txt1 And Range("B" & i).Value = txt2 Then
      Foundit = True
      Exit For
    End If
  Next
 
  If Foundit = False Then
    i = lr + 1
    Range("A" & i).Value = TextBox1.Value
    Range("B" & i).Value = TextBox2.Value
  End If
  Range("C" & i).Value = Range("C" & i).Value + TextBox3.Value
  Range("D" & i).Value = Range("D" & i).Value + TextBox4.Value
   
  TextBox1.Value = ""
  TextBox2.Value = ""
  TextBox3.Value = ""
  TextBox4.Value = ""
End Sub
Click to expand...
I already het it :) thank you very much Mr Dante. Your help is very meaningful to me because I try to study by myself all the VBA coding and encountered many errors to run it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Cell values in same column summed to different cells based on color
Replies
3
Views
119
dermie_72
D
Y
  • Question
Sum the total working hours when the Qty Completed become value except 0 automatically
Replies
0
Views
329
yoon
Y
W
  • Question
Sum value in a column based on condition
Replies
1
Views
104
SunnyAlv
SunnyAlv
Y
  • Question
one userform working for two sheets with different command.
Replies
8
Views
240
Bosquedeguate
Bosquedeguate
G
  • Solved
Finding previous number to sum
Replies
9
Views
121
Fluff
Fluff

Forum statistics

Threads
1,215,482
Messages
6,125,058
Members
449,206
Latest member
Healthydogs

Share this page

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