Search by filter using macro

[tmwilkin]

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 _
& "*"


--
Regards,
Tom Ogilvy


"tmwilkin" <[email protected]> wrote in message
news:[email protected]...
> 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.

[tmwilkin]

Worked perfectly! Thanks Tom.

"Tom Ogilvy" wrote:

> Selection.AutoFilter Field:=1, Criteria1:="=*" & Range("M13").Value _
> & "*"
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "tmwilkin" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

[wilby31]

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

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

Thank you,
Brent

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


--
Regards,
Tom Ogilvy


"tmwilkin" <[email protected]> wrote in message
news:[email protected]...
> 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.

[gmr7]

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?
--
gmr7


"Tom Ogilvy" wrote:

> Selection.AutoFilter Field:=1, Criteria1:="=*" & Range("M13").Value _
> & "*"
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "tmwilkin" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

[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.

gmr7 wrote:
>
> 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?
> --
> gmr7
>
> "Tom Ogilvy" wrote:
>
> > Selection.AutoFilter Field:=1, Criteria1:="=*" & Range("M13").Value _
> > & "*"
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "tmwilkin" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

--

Dave Peterson

[gmr7]

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"


--
gmr7


"Dave Peterson" wrote:

> 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.
>
> gmr7 wrote:
> >
> > 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?
> > --
> > gmr7
> >
> > "Tom Ogilvy" wrote:
> >
> > > Selection.AutoFilter Field:=1, Criteria1:="=*" & Range("M13").Value _
> > > & "*"
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "tmwilkin" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

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



gmr7 wrote:
>
> 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"
>
> --
> gmr7
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > gmr7 wrote:
> > >
> > > 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?
> > > --
> > > gmr7
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Selection.AutoFilter Field:=1, Criteria1:="=*" & Range("M13").Value _
> > > > & "*"
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "tmwilkin" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[gmr7]

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.
--
gmr7


"Dave Peterson" wrote:

> How about:
> Selection.AutoFilter Field:=1, Criteria1:=">=13", Operator:=xlAnd, _
> Criteria2:="<=70"
>
>
>
> gmr7 wrote:
> >
> > 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"
> >
> > --
> > gmr7
> >
> > "Dave Peterson" wrote:
> >
> > > 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.
> > >
> > > gmr7 wrote:
> > > >
> > > > 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?
> > > > --
> > > > gmr7
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > Selection.AutoFilter Field:=1, Criteria1:="=*" & Range("M13").Value _
> > > > > & "*"
> > > > >
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "tmwilkin" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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.
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[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

gmr7 wrote:
>
> 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.
> --
> gmr7
>
> "Dave Peterson" wrote:
>
> > How about:
> > Selection.AutoFilter Field:=1, Criteria1:=">=13", Operator:=xlAnd, _
> > Criteria2:="<=70"
> >
> >
> >
> > gmr7 wrote:
> > >
> > > 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"
> > >
> > > --
> > > gmr7
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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.
> > > >
> > > > gmr7 wrote:
> > > > >
> > > > > 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?
> > > > > --
> > > > > gmr7
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > Selection.AutoFilter Field:=1, Criteria1:="=*" & Range("M13").Value _
> > > > > > & "*"
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > > "tmwilkin" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[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 be adapted to have say, "greater than or equal to cell reference A4." I tried playing with the above examples but i must be doing something wrong.

Any help greatly appreciated.

Chris

[chris100]

Hi again,

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

Thanks again,

Chris