Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 391
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to copy from sheet1 then paste special transpose to sheet2,3,4 Christine Excel Discussion (Misc queries) 2 July 22nd 09 09:50 PM
copy data from sheet2 to sheet1 when sheet2 has variable # of rows Anne Excel Discussion (Misc queries) 6 February 27th 09 10:48 PM
Macro to Copy data from a list in sheet1 and paste into sheet2 Michael Excel Discussion (Misc queries) 3 April 23rd 08 06:52 PM
search Sheet2! for the contents of Sheet1! Fester Excel Discussion (Misc queries) 8 November 11th 06 02:09 AM
Search, find, copy from sheet1 and paste into sheet2 lothario[_47_] Excel Programming 4 November 9th 03 10:07 AM


All times are GMT +1. The time now is 11:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"