start macro recorder. set an autofilter on the
super_sheet and then add filters as you describe. Stop
the recording and examine the code.
Your own VBA should
(1) set the filter
(2) set the criteria
(3) copy the resultant visible rows
(3) remove the filter
HTH
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Hi,
I have:
Workbook called "Articles".
= It contains sheets "super_sheet" and "sub_sheet".
= "super_sheet" contains 20,000 rows of data.
= "super_sheet" contains the columns "Type", "From
Year", "To Year",
"Number", "Description", etc ...
= Where "Type" is column B, "From Year" is C, "To
Year" is D,
"Number" is G, "Description" is H
I would like to:
* Have a button in called "Find Articles"
in "sub_sheet".
* This button should take the search criteria from
cells "b3", "b4",
"b5", "b6" in "sub_sheet".
* Where cells "b3", "b4", "b5", "b6" in "sub_sheet" are
be
respectively labeled "Type", "Year", "Number"
and "Description".
* Where "b3", "b5" and "b6" may each contain may text.
Or they may be
blank.
* Where "b4" may contain a two digit number with no
decimals. Or it
may be blank.
* So when the "Find Articles" button is clicked then
the VBA code
would find the rows in "super_sheet" whe
the super_sheet:column B contains the text found in
sub_sheet:b3
AND super_sheet:column C has the number greater than or
equal to
found in sub_sheet:b4
AND super_sheet:column D has the number less than or
equal to
found in sub_sheet:b4
AND super_sheet:column G contains the text found in
sub_sheet:b5
AND super_sheet:column H contains the text found in
sub_sheet:b6
* Note: in the case of columns B, G and H the text
should just contain
the criteria text. There is no need for an exact match
or case
sensitivity.
* All rows in "super_sheet" that meet the above 5
conditions should be
copied and pasted into "sub_sheet" starting at cell e10.
* If there are any blanks in "sub_sheet"
cells "b3", "b4", "b5", "b6"
then the respective condition does not apply and the
remaining
contitions apply.
* If there are all blanks in "sub_sheet"
cells "b3", "b4", "b5", "b6"
then no conditions apply so do nothing.
Can you please give me the VBA code for this "Find
Articles" button?
Thanks,
Luther
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/
.