Do I have to repeat the same procedure to match combobox with two sheets and different columns

[Maklil]

Hi
I have this procedure

Private Sub ComboBox1_Change()
Dim ws As Worksheet, LR As Long, qtyP As Double, qtyT As Double
  If ComboBox1.Value <> "" Then
   Set ws = Sheets("ORDERS")
    LR = ws.Cells(Rows.Count, 3).End(3).Row
    qtyP = WorksheetFunction.SumIf(ws.Range("D2:D" & LR), ComboBox1.Value, ws.Range("E2:E" & LR))
   
   qtyT = qtyT + qtyP
   TextBox1.Text = qtyT
  End If
End Sub

this matches the item from combobox1 with column D and merge amount for column E and show in textbox1 for ORDERS sheet.
now I want at the same time match combobox1 with column B for column C contains amount and show amount in textbox2 for STOCK sheet.
so the question is , is there any procedure do that without repeat the same code for STOCK sheet ?

 

[dmt32]

Hi,

You should be able to utilise your existing code modified a little to include the additional requirement.

Untested but see if this update to your code will do what you want

Private Sub ComboBox1_Change()
    Dim ws          As Worksheet
    Dim lr          As Long, c As Long, i As Long
    Dim qty         As Double
    
    i = 1: c = 4
    If Me.ComboBox1.Value <> "" Then
        For Each ws In ThisWorkbook.Worksheets(Array("ORDERS", "STOCK"))
            lr = ws.Cells(ws.Rows.Count, c).End(xlUp).Row
            qty = WorksheetFunction.SumIf(ws.Cells(2, c).Resize(lr), Me.ComboBox1.Value, ws.Cells(2, c + 1).Resize(lr))
            Me.Controls("TextBox" & i).Text = qty
            i = 2: c = 2
            qty = 0
        Next ws
    End If
    
End Sub

Hopefully update will do what you want but adjust as required.

Note: I assume that you found it on the web but I have replaced your value 3 in the Range.End property used to find the last row with correct constant xlUp which has a value of -4162. I am not sure why some choose to use 3 in its place or why it even works but personally, I would always recommend using the supplied constants for a function.

You can read more here: Range.End property (Excel)



Hope helpful

Dave

 

[Maklil]

That's great ,Dave !

I assume that you found it on the web

That's correct.

I am not sure why some choose to use 3 in its place or why it even works

me too !
thank you so much for your help .

 

[dmt32]

thank you so much for your help .

most welcome & appreciate your feedback

Dave