Filter Macro

[MEPI]

I have been searching this newsgroup for several weeks now trying to
solve a problem I've been having with automating a filter. I read an
older post that referenced the excellent web site Contextures. It
appeared that the file AdvFilterSearchWord.xls from that site would do
the trick - but if I just entered "pen"; when it filters, it returns
"Pen Sets", and also "Pencils". I wanted a filter that would return
nothing (as there aren't any instances of "pen"). I changed the
formula in the criteria cell (J2) from "SEARCH" to "FIND" and it worked
great. It was very fussy about the case of what I was searching for.
If I tried "pen", it returned nothing, "pen sets" returned nothing,
"Pen sets" returned nothing and "Pen Sets" worked just fine! I then
changed some of the data under the "Item" column to 18, 118, 188, 18a,
18b, N18 (because that is the type of data that I will have in the
column that I will be filtering) and then I tried to filter on "18".
It returned everything that had an "18" in it! I have tried to modify
the VBA code but I know little to nothing about VBA and been
unsuccessful. I would guess that it is a data type problem? This has
to be a simple and trivial problem, but I have to admit that I am
stumped. Any help would be appreciated.

 

[Guest]

Doesn't sound like a VB problem. Sounds like a how to use the advanced
filter problem.

If I put

Header1
18


in as my criteria, it only returns rows that contain 18 in column Header1,
given the same choices you described.

 

[MEPI]

Tom,

I appreciate the feedback. I did go back out to the web site and
downloaded a fresh copy of the file - just in case I had corrupted my
copy. I looked all through that file and I do not see anything that
looks like "Header1". I do understand how to use advanced filters and
I did take the time to describe which file I was using and what I was
trying to accomplish.

I have been able to apply this type of filter to the data file that I
am working with (manually, and with a macro, and with a macro assigned
to a custom button on a toolbar). Here is my code:

Sub FilterIt()
'
' FilterIt Macro
' Macro recorded 4/14/2006
'
Range("A6").Select
Range("Database").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("A1:F2"), Unique:=True

End Sub

I know that it is trivial, but I just can not figure out how to do it
where I can simply enter the criteria into the cell "A1" (as in the
referenced file) hit enter and have it work.

I have seen previous posts from you and used some of your code (and
John Walkenbach's and Jon Pelletier's). If you could flesh out your
earlier post using the referenced file, I would appreciate this
opportunity to learn.

 

[MEPI]

My apologies to Jon Peltier on the spelling of your name.

 

[Guest]

I wasn't associating my response to any particular code on some site somewhere.

I made a small table with 3 columns with the names/headings:
header1 header2 header3

I populated the first column with values such as you described

I made a criteria range

header1
18

or
="18"

didn't seem to matter.

I then applied an advanced filter and only rows where column A contained 18
alone were visible.

 

[MEPI]

I'm sorry Tom - I have missed the mark with my quesion to this forum.
I'll try to clarify.

I have a spreadsheet with several columns of data. I wanted to make it
easy for some users to look at filtered data. I want them to be able
to enter a value into cell A1 - press enter and have the database
filtered on that value. They don't understand criteria ranges or
filters or advanced filters (and I'm not very swift either). I was
using this code (credit goes to Contextures.com and the code comes from
the file AdvFilterSearchWord.xls from that site)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Or _
Target.Address = "$G$1" Then
Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria"), Unique:=False
End If
End Sub
------------End code---------------------

This following formula appears in cell J2
=AND(OR($G$1="",D4=$G$1),ISNUMBER(SEARCH($A$1,E4)))

My problem: If I enter an 18 into cell A1, this will return anything
with an 18 (18, 188b, 18a, 118, you get the idea). I would appreciate
it if you (or anyone) could point out my obvious (and probably trivial)
mistake(s).

 

[Tom Ogilvy]

Your criteria is this:

=AND(OR($G$1="",D4=$G$1),ISNUMBER(SEARCH($A$1,E4)))

Which searches each item in column E of the Database and if it finds the
value in cell A1 (18) within that value, it meets the criteria. So all the
values you complain about do meet the criteria you have set. It is working
the same way it should have worked with the words in column ITEM where Pen
in A1 returned Pencil and Pen Sets

 

[MEPI]

Tom,
You are 100% correct...my problem is that I wanted it to only filter on
18. Originally, I had changed the keyword SEARCH to FIND and it worked
with Pen etc (please see my first posting). You pointed out that the
formula finds the value in cell A1 (18) "within" that value. The piece
I seem to be missing (which is probably obvious) is what keyword do I
need in the formula that finds the value in cell A1 (18) and only that
value? Or what do I need to modify in the VBA code? Thank you for
your patience on what must be an obvious solution.

 

[MEPI]

Tom,
Got it!
=(OR($A$1="",E4=$A$1)) works just fine. I did not need either the
SEARCH or FIND function. It was an advance filter problem afterall.
Just takes some of us longer to figure things out than others. Thanks
for putting me on the correct track!