Modify the search code in the form

[Hany ali]

hello my master,I Want You Help to correct Search Code in Userform5 to Search on two conditions ,by Bond Type in ComboBox6 & Bond No. in ComboBox7

Private Sub ComboBox7_Change()
Dim LR As Long
Dim Mh As Long
Dim iCont As Integer
Dim r As Integer
Dim c As Integer
Dim ii As Double
ii = Me.ComboBox7.Value
'==================================
KH_ClearControl
'==================================
With Sheets("Sheet2")
    LR = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
    Mh = WorksheetFunction.Match(ii, .Range("D2:D" & LR), 0) + 3
    iCont = WorksheetFunction.CountIf(.Range("D2:D" & LR), ii)
End With
Me.TextBox3.Value = Sheets("Sheet2").Cells(Mh, 1).Value
Me.ComboBox1.Value = Sheets("Sheet2").Cells(Mh, 2).Value
Me.ComboBox2.Value = Sheets("Sheet2").Cells(Mh, 5).Value
'==========
For r = 1 To iCont
    For c = 1 To 2
    Adr = Cells(r, c).Address(0, 0)
   Me.Controls(Adr).Value = Sheets("Sheet2").Range("C" & Mh).Cells(r, c).Value
 Next
 Next
End Sub

 

[Hany ali]

Is there a solution or is the topic difficult?

 

[Hany ali]

Is there a solution to my distinguished teachers?

 

[Marc L]

Create at least an enough smart worksheet with no blank in columns A:C​

and / or add an ID column containing the concatenation of these columns (could be done via a beginner level formula) so easier then to filter …​

​

According to the actual data sheet you can use the search Excel feature (see Range.Find in the inner VBA help and its sample)​

to find a value within the Date column for example then for each matching date check the other columns​

or loop each data row an check columns A:C, should be easier with a sorted worksheet …​

​

 

[Hany ali]

thanks alot , Is that what you mean, professor?

 

[Marc L]

Yes as now your worksheet follows the Excel spirit, could be better with data sorted …​

​

So why an user form as Excel basics like filter / advanced filter can do the job ?​

​

(By the way I'm very not a professor …)​

​

 

[Hany ali]

Thanks very Much , This is a great humility from you

 

[Hany ali]

This method did not work for me, so is there a solution in the same way as the form ?

 

[Marc L]

This method well works on my side, so far easier than using an user form …​

​

Anyway both working methods between others :​

• via an advanced filter to extract data to another place / sheet in order to be used to fill the combo box.
  
  
• Via a loop using the Range.Find method like explained in the VBA inner help and its example …
  As yet stated in post #4, the easy way is an helper column with the concatenation of the keys to search (seems to be columns A:C).
  Without this helper column, you must search first for a key, for example in the first column.
  Once a key is found, check the other keys on the same row and if all are matching then fill the combo box with this row …

 

[Hany ali]

thanks alot ,but
The problem is that I am not a professional in this
So I could not do this

 

[Marc L]

As reading the VBA inner help of Range.Find is at beginner level or like operating manually​

an advanced filter and getting a code base via the Macro Recorder, both are Excel Basics …​

​

Anyway, first well think about an analyse of your need then explain it clearly here with your strategy you want to apply …​

​