using autofilter to search 2 columns

[neowok]

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

 

[arno]

Hi,

you should add one column that combines the 2 other columns as you wish.
Then you can filter based on that new column.

arno

 

[neowok]

cant do that they have to be 2 seperate columns and are change
regularly.

i just tried a plain autofilter from th data menu then went to custom
and for some reason autofilter is refusing to filter on that colum
properly. if i select contains 6, it comes up with no results when th
column contains dozens of 6's. if i try the same thing on the nex
column it works perfectly. the ONLY difference between the 2 column
is that the one that doesnt work only has numbers in it (which seem t
be right aligned even though theres no formatting on the cells) and th
column that works fine has letters in the cells and is left aligned
even though i havent formatted it to be. yet both are down a
"general" number format when going to cell properties so they should b
identical.

i just formatted the column that only contains numbers in the cells
and told it the format is 'text'. and its STILL not working

 

[arno]

cant do that they have to be 2 seperate columns and are changed
regularly.

believe me, having a dummy-column with a formula combining the other 2
columns will solve all of your problems. you just have to make sure to copy
the formula far enaough. your filter does not work because your data is a
mixture of text and numbers.

arno

 

[JE McGimpsey]

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

 

[neowok]

hmm i cant get that to work.

a sample of the tables im using i
http://www.darkcity.nildram.co.uk/listtest.xls

it isnt the case on that sample but sometimes there will be somethin
in the lul ref column AND the tl ref column (so it needs to list it i
either column contains the typed value)

 

[Tom Ogilvy]

Autofilters don't work that way. Conditions applied on separate columns are
additive (Logical AND conditions).

If you have lul set for non blanks and tl set for non blanks, then you would
only see rows where both lul and tl were non blank.

You can get what you want using an Advanced filter, but you have to specify
the critieria in a range of cells (it doesn't have to be on the same sheet.)

If you want an example, contact me with your email address and I will set up
a sample on your file and mail it to you.

(e-mail address removed)