merge QTY on textbox based on selected combobox on userform if the adjacent cell doesn't equal zero

[abdo meghari]

Hi
I want searching the BRAND in in column E for AS sheet based on selected from combobox1 and if doesn't contain zero in column H then merge duplicates BRAND IN column F in textbox1
the sheet

AA Microsoft Excel ‫(3)‬.xlsx
	E	F	G	H
1	BRAND	QTY	UNIT PRICE	TOTAL
2	BS 1200R20 G580 JAP	500	1500	0
3	BS 1200R20 G580 THI	300	1600	0
4	BS 1200R20 R187 JAP	150	1450	0
5	BS 1200R24 G580 JAP	260	2000	0
6	BS 1200R20 G580 JAP	340	1550	527000
7	BS 1200R20 G580 THI	800	1440	1152000
8	BS 315/80R22.5 R184 THI	500	1250	625000
9	BS 1200R20 G580 JAP	300	1600	480000
10	BS 1200R20 G580 THI	250	1700	425000
AS

Cell Formulas
Range	Formula
H6:H10	H6	=IF(AND(IN!$C$7=C6,ISNUMBER(MATCH($E6,IN!$C$3:$C$300,0))),0,F6*G6)

so when select ID from column E then will merge QTY F in textbox1 if the ID doesn't contain zero in adjacent cell for column H

I have this code to populate the QTY in textbox1 without merge duplicates ID .

Private Sub ComboBox1_Change()
Dim c As Range
    Dim ws As Worksheet
    Set ws = Sheets("AS")
With ws
   
    Set c = .Range("E:E").Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
          TextBox1.Value = c.Offset(, 1).Value
          End If
          End With
End Sub

I hope some body help

 

[DanteAmor]

Try this:

Private Sub ComboBox1_Change()
  Dim i As Long
  Dim tot As Double
  Dim ws As Worksheet
 
  Set ws = Sheets("AS")
  For i = 2 To ws.Range("E" & Rows.Count).End(3).Row
    If ws.Range("E" & i).Value = ComboBox1.Value And _
      ws.Range("H" & i).Value <> 0 Then
      tot = tot + ws.Range("F" & i).Value
    End If
  Next
  TextBox1.Value = tot
End Sub

If there are many records, try the following:

Private Sub ComboBox1_Change()
  Dim r As Range, f As Range, cell As String
  Dim tot As Double
  Dim ws As Worksheet
  
  Set ws = Sheets("AS")
  Set r = ws.Range("E2", ws.Range("E" & Rows.Count).End(3))
  Set f = r.Find(ComboBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      If ws.Range("H" & f.Row).Value <> 0 Then
        tot = tot + ws.Range("F" & f.Row).Value
      End If
      Set f = r.FindNext(f)
    Loop While f.Address <> cell
  End If
  
  TextBox1.Value = tot
End Sub

 

[abdo meghari]

Hi Dante
I prefer using second code , it works very well .
thank you so much.