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.

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.

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.

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

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.

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

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

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