Search by filter using macro
 

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.

Search by filter using macro
 

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


--
Regards,
Tom Ogilvy


"tmwilkin" wrote in message
...
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.

Search by filter using macro
 

Worked perfectly! Thanks Tom.

"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy


"tmwilkin" wrote in message
...
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.

Search by filter using macro
 

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


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


--
Regards,
Tom Ogilvy


"tmwilkin" wrote in message
...
I have a spreadsheet containing a list of products for referenc
purposes.
This list will often be utilized by searching for data in one colum
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 clickin
the
filter
arrow, selecting "custom", etc. ... I was hoping I could just set u
a
cell
where a macro would grab that value and use it for the searc
criteria -
that
way they could type the search keywords and hit a button fo
results.

I've found though that although while recording the macro I can cop
and
paste the value from a cell into the filter area by using shortcu
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, i
doesn't
paste
the value of that cell in the filter area, it only just puts dow
the
value
that was input when I created the macro. Cell references don't see
to be
available to use there either. I know this process would be easie
in
Access, but I would prefer to keep this in Excel.

Any ideas? Thanks

--
wilby3
-----------------------------------------------------------------------
wilby31's Profile: http://www.excelforum.com/member.php...fo&userid=2728
View this thread: http://www.excelforum.com/showthread.php?threadid=47218

Search by filter using macro
 

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" wrote in message
...
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.

Search by filter using macro
 

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" wrote in message
...
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

Search by filter using macro
 

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" wrote in message
...
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

Search by filter using macro
 

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" wrote in message
...
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

Search by filter using macro
 

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" wrote in message
...
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

Search by filter using macro
 

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" wrote in message
...
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

Search by filter using macro
 

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

--
chris10
-----------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516
View this thread: http://www.excelforum.com/showthread.php?threadid=47218

Search by filter using macro
 

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

--
chris10
-----------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516
View this thread: http://www.excelforum.com/showthread.php?threadid=47218