search userform help please

Hi all ..

I am trying to create a userform to return a value in a listbox using a textbox search .. The workbook has thousands of rows and i need the search to filter out as much as possible ... i also need to be able to use several * wildcards in each the search.



I have attached a sample of what i have so far

Thanks in advance
Clarkey

Thanks Mehmetcik
Unfortunately your search suggestion will not work for my need ...
I have 2 columns A:part Number B:Description
my specific need is that My userform has a textbox for search input and a listbox to display the results..
So..
I want to be able to type in the search textbox parts of the description using wildcards for missing parts of the description and a selection of possibilities to my search be displayed in the listbox..

There is several thousand parts in the full worksheet to be searched with several similar descriptions ..
The problem with what i have is if i put in *AC* in the search i get a couple of hundred results if i then put in *AC*1/8* i can narrow the results to a hundred or so if i then search *AC*1/8*10.0* i get nearly the same number of results and returns some results without 10.0 in the description .... It seems to max out on number of wildcards i can use in my search

Thanks Anyway

Hi CLARKEY1,
perhaps this example will approach for you

Nilem,

That is pretty close but i need to know the part number in column A as well in the lower text box

Thanks

Where is the lower text box? Can you show an example?

See if this is what you wanted.

Hi Millz,

I cant get that to search anything ... unless im missing something ... I put *AC* in search and "no results found" returns

Nilem,

I mean listbox .. your search worked fine apart from in the listbox i need the part number and description ... not just description

I thought you only wanted to look through the part numbers, lol.

Try this amended:

Millz,

Still having same issue .... not sure if im doing it wrong ......

what i need is ...

i need to type my search terms in the text box, some of the characters may be wild cards(*).. this is only going to refer to description column (B) on worksheet ... hit search ... and the textbox will populate with part numbers and descriptions that match that search

Building on nilem's code:

Option Explicit
Option Compare Text
Dim x

Private Sub UserForm_Initialize()
    x = Range("a1", Cells(Rows.Count, 2).End(xlUp)).Value
End Sub

Private Sub TextBox1_Change()
Dim i As Long, s As String, txt As String, lt As Long

txt = TextBox1.Text: lt = Len(txt)
If lt = 0 Then Exit Sub
For i = 1 To UBound(x)
    If x(i, 2) Like "*" & txt & "*" Then s = s & "~" & x(i, 1) & vbTab & x(i, 2)
Next i

Me.ListBox1.List = Split(Mid(s, 2), "~")
End Sub

Private Sub ListBox1_Click()
If ListBox1.ListIndex = -1 Then Exit Sub
Columns(2).Find(Split(ListBox1, vbTab)(1), lookat:=xlWhole).Select
End Sub

Private Sub CommandButton1_Click(): Unload Me: End Sub

@millz, try to avoid using .AddItem when you have more than 5 items, it's extremely slow; also when searching through a range, copy it to an array first and loop through that. Looping though a range on the worksheet is also sloooow

Kyle .....

Yay .... That seems to do the trick .... Thanks

After a little people testing we came to the conclusion that Nilem's code and system worked well but took too long to load and search as there is over 40,000 parts in the full catalogue .. So went back to my original search userform ... which works sort of ok and usable ... then it was suggested that we should be able to search in the oposite way ... ie. search for a part number including wildcards (*) in textbox2 that will populate listbox2 with the completed part number and description ... I have completed the userform to reflect this change but can't get the partnumber search to work ...
I have attached what i have so far and any help would be much appreciated
please be aware this attachement is only a small part of the complete list of parts that has over 40,000 rows

Many Thanks

Don't you have a database? Excel is for adding things up...

Hi CLARKEY1,
try it (with using Kyle's code)

Option Explicit
Option Compare Text
Dim x

Private Sub UserForm_Initialize()
    x = Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

Private Sub CommandButton1_Click() 'DESCRIPTION SEARCH, ListBox1
FilterData Me.TextBox1.Value, 2
End Sub

Private Sub CommandButton2_Click() 'PART NUMBER SEARCH, ListBox2
FilterData Me.TextBox2.Value, 1
End Sub

Private Sub FilterData(txt As String, k As Long)
If txt = vbNullString Then Exit Sub
Dim i As Long, s As String
For i = 1 To UBound(x)
    If x(i, k) Like "*" & txt & "*" Then s = s & "~" & x(i, 1) & vbTab & x(i, 2)
Next i
If k = 2 Then Me.ListBox1.List = Split(Mid(s, 2), "~") Else Me.ListBox2.List = Split(Mid(s, 2), "~")
End Sub

Thanks All,

This now works exactly how i need it to ....... 1 question though .. why do some of the descriptions populate the text box correctly and some dont have a space between part number and description ..... if anyone would like to try .. search for W109* in part number search and view the results, (This happens in both part number search and description search) ..... this is a minor issue and not something i can't live with ... would just be nice to tidy up .... Apart from that ..... Thanks to all who have helped ........

I have attached a very small sample to view

DOH .... Changed the font and it all worked ...... SOLVED .... Thanks All