I have a bit of code which ive used to get autofilter to search a colum
of a bunch of tables for rows where the cell in that column 'contains
the text that the user has selected in a combobox, and then shows al
rows with this text.

What i am trying to do now is adapt this code so that i do the sam
thing except with TWO columns, and the text its searching for will b
what the user has typed into a textbox.

Ideally it will do this as the user types in the text, i.e. they type
6 and it lists all the relevant rows containing a 6 anywhere in eithe
of the 2 columns im searching in, then type 5 and it re-does the searc
to be anything with *65* in that column etc. This may be too much fo
excel to handle making it slow or something, so if this is a bad way t
do it then it would be nice to make it seatch when the user presses th
enter key after typing their text in the textbox.

below is the code ive got so far, it only searches column 2, but i nee
it to search column 3 as well. It also re-does the search every tim
they type or delete a character (because its in textbox change event)
but for some reason i cant get it to search for 'contains', it onl
seems to produce a result when an exact match is found rather tha
anything containing the typed text, dont know why.

Private Sub TextBox1_Change()
Dim i As Long

If Me.TextBox1.Text = "" Then
Me.AutoFilterMode = False
Else
Me.Range("mytables").AutoFilter 2, _
"=*" & Me.TextBox1.Text & "*", xlAnd, , False

With Me.Range("mytables")
For i = 1 To 8
If i < 2 Then
.AutoFilter i, , , , False
End If
Next i
End With
End If

End Sub

(the 'with' code just goes along and turns off the dropdowns on each o
the 8 columns, 'mytables' is the cells containing the 4 tables i
searching which are one above the other).

anyone help with this

--
Message posted from http://www.ExcelForum.com