Search by filter using macro

[Guest]

I have a spreadsheet containing a list of products for reference purposes.
This list will often be utilized by searching for data in one column to see
if it exists and in many cases there will be multiple matches.

I can do this by using a Custom auto filter using the "contains" operator.
In an effort to not make people go through the process of clicking the filter
arrow, selecting "custom", etc. ... I was hoping I could just set up a cell
where a macro would grab that value and use it for the search criteria - that
way they could type the search keywords and hit a button for results.

I've found though that although while recording the macro I can copy and
paste the value from a cell into the filter area by using shortcut keys, the
macro script shows it as a static value, not a paste command. Therefore each
time I run the macro using different keywords in the cell, it doesn't paste
the value of that cell in the filter area, it only just puts down the value
that was input when I created the macro. Cell references don't seem to be
available to use there either. I know this process would be easier in
Access, but I would prefer to keep this in Excel.

Any ideas? Thanks.

 

[Tom Ogilvy]

Selection.AutoFilter Field:=1, Criteria1:="=*" & Range("M13").Value _
& "*"

 

[Guest]

Worked perfectly! Thanks Tom.

Selection.AutoFilter Field:=1, Criteria1:="=*" & Range("M13").Value _
& "*"

 

[wilby31]

Hi, I am looking to do what I think is the exact same thing. I have a
Excel database organized by a serial number and I want the user to b
able to type the number in a specific cell and the total list to filte
down to just the rows associated with the desired serial number.

I am unsure how to implement the advice you gave the person who aske
this of you in the original post. Perhaps you could provide some mor
detail as how to use your code snippet. I am vaguely familar wit
macro recording. If you feel I should learn more about recordin
macros, any advice on where to start would be appreciated.

Thank you,
Brent

 

[Guest]

I have similar problem with the "contains" operator in a custom autofilter
macro, but a little different than previous question. I have a list of values
13.0069, 13.0070, 14.0051, 14.0065, 15.0063 & so on (a pretty long list). I
want to filter based upon the first 2 numbers (13, 14, 15, ...). I would
rather not setup the criteria up for each, is there anyway this can be done
in one or a few statements like you did previously.

I guess I should also ask this real quick, is there a limit to the # of
cells one can filter?

 

[Dave Peterson]

If those are really numbers, can't you just filter by:

Greater than or equal to 13
and
less than or equal to 16
(or whatever??)

You can record a macro when you do it if you need the code.

 

[Guest]

I am stuck on the code for each number that I want autofiltered and copied
(i.e., 13, 14, up 70). This is the autofilter portion of the code I have,
can I make this code for the multiple numbers?



Selection.AutoFilter Field:=1, Criteria1:=">=13", Operator:=xlAnd, _

Criteria2:="<14"

 

[Dave Peterson]

How about:
Selection.AutoFilter Field:=1, Criteria1:=">=13", Operator:=xlAnd, _
Criteria2:="<=70"

 

[Guest]

That works for the range 13-70, but I am looking to do filter for *13 and
then copy that range elsewhere, then do the the *14 and copy that range
elsewhere, and so on.

 

[Dave Peterson]

Debra Dalgleish and Ron de Bruin have samples that you may like. Ron's addin
may be sufficient right out of the box.

Debra's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

And Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm

That works for the range 13-70, but I am looking to do filter for *13 and
then copy that range elsewhere, then do the the *14 and copy that range
elsewhere, and so on.

 

[chris100]

I did this a while ago yet i can't find the post i found it from again


With reference to the above method of filtering, how can the method b
adapted to have say, "greater than or equal to cell reference A4."
tried playing with the above examples but i must be doing somethin
wrong.

Any help greatly appreciated.

Chri

 

[chris100]

Hi again,

Sorry for bringing this back up to the top again but i'm desperate t
find a solution for this so i canfinish this project for implementatio
by Monday (aaah)

Thanks again,

Chri