vba sumif into textbox on userform

[abdelfattah]

hi, everyone
i have sheet contains data begins a3:h10000 i try writing code about function sumif to show me the total values into textbox2 , textbox3 it gives me total=0 of course it also contains the userform combobox 1 when i choose additem in combobox1 it suppose gives me the total in textbox2,3 the total in textbox2 it depends on 2 columns d= values and h= name textbox3 it deoends on 2 columns e= values h= name
this is my code

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim Rng As Range
Dim Sel
Set ws = Sheets("sheet2")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
 Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)
 If Not Rng Is Nothing Then
 Me.TextBox1.Value = ws.Cells(Rng.Row, "c")
Me.TextBox2.Value = Application.WorksheetFunction.SumIf(Range("d:d"), Me.ComboBox1, Range("g:g"))
Me.TextBox3.Value = Application.WorksheetFunction.SumIf(Range("e:e"), Me.ComboBox1, Range("g:g"))
Me.TextBox4 = Val(Me.TextBox2.Value) - Val(Me.TextBox3.Value)
 
End If
End Sub

 

[DanteAmor]

You can put a sample of your data

Upload an excel range:
XL2BB - Excel Range to BBCode

 

[abdelfattah]

You can put a sample of your data

Upload an excel range:
XL2BB - Excel Range to BBCode

thanks but i would code not formula i hope help me

 

[DanteAmor]

thanks but i would code not formula i hope help me

?

I just want to see a sample of your data.

For example:

Book1
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	G
2	A2	D2	C2	D2	E2	F2	1
3	A3	B3	C3	D3	E3	F3	2
4	A4	D2	C4	D2	E4	F4	3
5	A5	B5	C5	D5	E5	F5	4
6	A6	D2	C6	D2	E6	F6	5
7	A7	B7	C7	D7	E7	F7	6
8	A8	D2	C8	D2	E8	F8	7
sheet2

According to your code:
You are looking for the combobox1 data in column "B"
Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)

If it exists then you add the values of column G if the data in column D is equal to combobox1.

In my example. In cell B2 I have the data "D2".
In combobox1 I have "D2". Then the first condition is met.
Then add the values 1 + 3 + 5 + 7 = 16

The code works, but I don't know what data you have.

 

[abdelfattah]

these my data

 

[DanteAmor]

Try this:

Updated code:
- Columns(7)
- Missing "End if"
- Missing ws.Range("G:G")
- ws.Range("G:G"), Me.ComboBox1, ws.Range("D:D")

Private Sub ComboBox1_Change()
  Dim ws As Worksheet, Rng As Range, Sel As Variant
  Set ws = Sheets("sheet2")
  Sel = Me.ComboBox1.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(7).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
      Me.TextBox1.Value = ws.Cells(Rng.Row, "C")
      Me.TextBox2.Value = WorksheetFunction.SumIf(ws.Range("G:G"), Me.ComboBox1, ws.Range("D:D"))
      Me.TextBox3.Value = WorksheetFunction.SumIf(ws.Range("G:G"), Me.ComboBox1, ws.Range("E:E"))
      Me.TextBox4 = Val(Me.TextBox2.Value) - Val(Me.TextBox3.Value)
    End If
  End If
End Sub

 

[abdelfattah]

i'm s

Try this:

Updated code:
- Columns(7)
- Missing "End if"
- Missing ws.Range("G:G")
- ws.Range("G:G"), Me.ComboBox1, ws.Range("D:D")

Private Sub ComboBox1_Change()
  Dim ws As Worksheet, Rng As Range, Sel As Variant
  Set ws = Sheets("sheet2")
  Sel = Me.ComboBox1.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(7).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
      Me.TextBox1.Value = ws.Cells(Rng.Row, "C")
      Me.TextBox2.Value = WorksheetFunction.SumIf(ws.Range("G:G"), Me.ComboBox1, ws.Range("D:D"))
      Me.TextBox3.Value = WorksheetFunction.SumIf(ws.Range("G:G"), Me.ComboBox1, ws.Range("E:E"))
      Me.TextBox4 = Val(Me.TextBox2.Value) - Val(Me.TextBox3.Value)
    End If
  End If
End Sub

i'm sorry i forget to inform the code this is relating another sheet to show the names in combobox

Dim ws As Worksheet
Dim Rng As Range
Dim Sel
Set ws = Sheets("sheet2")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
 Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)
 If Not Rng Is Nothing Then
 Me.TextBox1.Value = ws.Cells(Rng.Row, "c")

 

[DanteAmor]

Then:

Private Sub ComboBox1_Change()
  Dim ws As Worksheet, Rng As Range, Sel As Variant
  Set ws = Sheets("sheet2")
  Sel = Me.ComboBox1.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
      Me.TextBox1.Value = ws.Cells(Rng.Row, "C")
      Me.TextBox2.Value = WorksheetFunction.SumIf(Range("G:G"), Me.ComboBox1, Range("D:D"))
      Me.TextBox3.Value = WorksheetFunction.SumIf(Range("G:G"), Me.ComboBox1, Range("E:E"))
      Me.TextBox4 = Val(Me.TextBox2.Value) - Val(Me.TextBox3.Value)
    End If
  End If
End Sub

 

[abdelfattah]

Then:

Private Sub ComboBox1_Change()
  Dim ws As Worksheet, Rng As Range, Sel As Variant
  Set ws = Sheets("sheet2")
  Sel = Me.ComboBox1.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
      Me.TextBox1.Value = ws.Cells(Rng.Row, "C")
      Me.TextBox2.Value = WorksheetFunction.SumIf(Range("G:G"), Me.ComboBox1, Range("D:D"))
      Me.TextBox3.Value = WorksheetFunction.SumIf(Range("G:G"), Me.ComboBox1, Range("E:E"))
      Me.TextBox4 = Val(Me.TextBox2.Value) - Val(Me.TextBox3.Value)
    End If
  End If
End Sub

thanks so much it's perfect

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.