one way, using a helper column:

Private Sub TextBox1_Change()
With Sheets("Sheet3")
.Range("I1").Value = TextBox1.Text
.Range("I2").Formula = _
"=AND(ISERR(SEARCH(I$1,B2)),ISERR(SEARCH(I$1,C2))) "
.Range("I2").AutoFill _
Destination:=.Range("I2:I" & .Range("B" & _
Rows.Count).End(xlUp).Row), _
Type:=xlFillDefault
Range("A1").AutoFilter _
Field:=9, _
Criteria1:="FALSE", _
VisibleDropDown:=False
.Columns(9).Hidden = True
End With
End Sub


In article ,
neowok wrote:

I have a bit of code which ive used to get autofilter to search a column
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 all
rows with this text.

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

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

below is the code ive got so far, it only searches column 2, but i need
it to search column 3 as well. It also re-does the search every time
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 only
seems to produce a result when an exact match is found rather than
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 of
the 8 columns, 'mytables' is the cells containing the 4 tables im
searching which are one above the other).

anyone help with this?


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