Multi-Criteria Search Box for dynamic listbox

[bmu13]

I am using 2 textboxes, 1 listbox, 1 button. I simply want to show a list that contain what is entered in the textboxes.

textbox1 = color
textbox2 = shape

		TEXTBOX 1	TEXTBOX 2
	A	B	C
1	BLUE-CIRCLE	BLUE	CIRCLE
2	GREEN-TRIANGLE	GREEN	TRIANGLE
3	RED-TRIANGLE	RED	TRIANGLE
4	RED-SQUARE	RED	SQUARE
5	BLUE-SQUARE	BLUE	SQUARE
6	RED-TRIANGLE	RED	TRIANGLE
7	GREEN-CIRCLE	GREEN	CIRCLE
8	GREEN-SQUARE	GREEN	SQUARE
9	GREEN-SQUARE	GREEN	SQUARE

<tbody>
</tbody>

What I have:
What works:

If textbox1.value <> "" and textbox2.value = "" then
set rng = .range("B1:B9")
set fnd = rng.Find(textbox1.value)
Listbox1.clear
if fnd is nothing then ListBox1.AddItem "Result Not Found": Exit Sub
Set first = fnd
With Listbox1
Listbox1.clear
Listbox1.AddItem fnd.Offset(, -1)
Do
Set fnd = rng.FindNext(fnd)
If fnd.Address = First.Address then Exit Do
ListBox1.AddItem fnd.Offset(,-1)
Loop
End With

What doesn't work

Elseif textbox1.value <> "" and textbox2.value <> "" then
set rng.Range("A1:C9")
ctr1 = 1
For i = 1 To 9
If StrComp(textbox1.value, rng(i, 2)) <> 0 then
arr1(ctr1) = i
ctr1 = ctr1 + 1
End If
Next i
ctr2 = 1
For j = 1 To Len(arr1)
If StrComp(textbox2.value, rng(arr1(j), 3)) = 0 then
arr2(ctr2) = arr1(j)
output(ctr2, 1) = rng(arr1(j), 3)
ctr2 = ctr2 + 1
End if
Next j
Listbox1.clear
Listbox1.List = output
End if


What I want:

textbox1.value = red textbox1.value = red
textbox2.value = "" textbox2.value = triangle

	A	B	C
3	RED-TRIANGLE	RED	TRIANGLE
4	RED-SQUARE	RED	SQUARE
6	RED-TRIANGLE	RED	TRIANGLE

<tbody>
</tbody>

	A	B	C
3	RED-TRIANGLE	RED	TRIANGLE
6	RED-TRIANGLE	RED	TRIANGLE

<tbody>
</tbody>

Elseif

Listbox1

Red-Triangle Red-Square Red-Triangle

<tbody>
</tbody>

Elseif

Listbox1

Red-Triangle Red-Triangle

<tbody>
</tbody>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG15Aug09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray(), c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
ListBox1.Clear
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] UCase$(Dn.Value) = UCase$(TextBox1.Value) And UCase$(TextBox2.Value) = UCase$(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ReDim Preserve Ray(c)
            Ray(c) = Dn.Offset(, -1)
        [COLOR="Navy"]ElseIf[/COLOR] UCase$(Dn.Value) = UCase$(TextBox1.Value) And UCase$(TextBox2.Value) = "" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ReDim Preserve Ray(c)
           Ray(c) = Dn.Offset(, -1)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
ListBox1.List = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[bmu13]

Mick, thank you! Your code worked perfectly. Now, the only thing missing is the scenario in which the user inputs a value that doesn't exist within the range. For example, if textbox1 = pink or textbox2 = cube. then the listbox should say "Result Not Found". How would you make this slight modification?

 

[MickG]

Try Changing the End of the code as shown in Red:-

Next Dn
[B][COLOR=#FF0000]If c > 0 Then
    ListBox1.List = Application.Transpose(Ray)
Else
     MsgBox "No Data Found!!"
End If[/COLOR][/B]
End Sub