• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Modify the search code in the form

Hany ali

Active Member
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
Code:
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
 

Attachments

Marc L

Excel Ninja
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 …​
 

Marc L

Excel Ninja
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 …)
 

Marc L

Excel Ninja
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 …
 

Marc L

Excel Ninja
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 …​
 
Top