Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple search criteria to find and copy from sheet1 and paste into sheet2
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple search criteria to find and copy from sheet1 and paste into sheet2
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple search criteria to find and copy from sheet1 and paste into sheet2
Hi Patrick, I tried your suggestion in super_sheet. In the AutoFilter dialogbox, I specified contains and =sub_sheet2!$B$3 This does not work. None of the rows are selected in super_sheet. The =sub_sheet2!$B$3 reference does not work. What am I doing wrong? On the other hand when I specify contains and rro I get the correct results because all the rows in that column tha contain the text rro are displayed. But I cannot use a hard value like rro. I need to use the cell reference. Please tell me how to correctly enter the =sub_sheet2!$B$3 cel reference in the AutoFilter dialogbox. Thanks. Luthe ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy from sheet1 then paste special transpose to sheet2,3,4 | Excel Discussion (Misc queries) | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
Macro to Copy data from a list in sheet1 and paste into sheet2 | Excel Discussion (Misc queries) | |||
search Sheet2! for the contents of Sheet1! | Excel Discussion (Misc queries) | |||
Search, find, copy from sheet1 and paste into sheet2 | Excel Programming |