I have I problem I cant solve

[j.morrison]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/JMORRI%7E1/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} h1 {mso-style-next:Normal; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; page-break-after:avoid; mso-outline-level:1; font-size:12.0pt; font-family:"Times New Roman"; mso-font-kerning:0pt;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> I have a real dilemma. I have two worksheets. Worksheet #1 which is named “Animals” and Worksheet#2 named “FilterCriteria”. What I am trying to do is write a macro which will start with the first entry in the FilterCriteria worksheet (Goldfish) and load that as the filter criteria. Then go to the Animal worksheet and find all of the cells under the “Animal” column and if the cell equals the filter criteria (Goldfish) then the value under the species column on the Animal worksheet needs to be changed to the species from the FilterCriteria worksheet. So if cell equals the filter criteria (Goldfish) then the value under the species column on the Animal worksheet equals “Fish”. So in the example below it should find 3 entries and there should be “fish” under the species columns on those rows. Then the macro will load the next filter criteria in the FilterCriteria worksheet (Herring) and load that as the filter criteria. Then go to the Animal worksheet and find all of the cells under the “Animal” column and if the cell equals the filter criteria (Herring) then the value under the species column on the Animal worksheet needs to be changed to the species from the FilterCriteria worksheet. So if cell equals the filter criteria (Herring) then the value under the species column on the Animal worksheet equals “Fish”. So in the example below it should find 1 entries and there should be “fish” under the species columns on those rows. And so forth until the end of the filter criteria list is reached at that point the macro terminates. The catch is the fiter criteria list on the FilterCriteria worksheet needs to be dynamic so that entries can be added or removed. I have tried several different means of achieving this but have met with no success. Can anybody help me?
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Worksheet #1 Name: Animals
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Animal Species

Goldfish
Black Widow
Herring
Finch
Zebra
Cow
Tiger
Lizard
Snake
Kangaroo
Goldfish
Herring
Polar Bear
Black Bear
Bobcat
Alligator
Shark
Whale
Monkey
Ape
Turtle
Shark
Goldfish
Bass
Carp
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Worksheet #2 Name: FilterCritia
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Animal Species

Goldfish Fish
Herring Fish
Tiger Mammal
Lizard Reptile
Kangaroo Mammal
Peacock Bird
Black Bear Mammal
Bobcat Mammal
Shark Mammal
Ape Mammal
Turtle Reptile
Bass Fish
Carp Fish
[FONT=&quot]Trout Fish[/FONT]

 

[nparkin]

Hi,
I think you need a VLOOKUP formula rather than a macro.

From what I understand you have two sheets. The filter criteria sheet and an Animals Sheet

In the Filter Criteria Sheet there are two columns Column A with the Animal and Column B with the Species.

In the Animals Sheet there are also two columns, Column A with the Animal and Column B you require the species to be added, but based on the value of the criteria in the Filter Criteria for each animal in the corresponding Column A.

In Cell B2 on the Animals Spreadsheet try the formula
=IF(ISNA(VLOOKUP(A2,'Filter Criteria'!$A:$B,2,0)),"",VLOOKUP(A2,'Filter Criteria'!$A:$B,2,0))

Copy this formula down as far as you

This is based on the assumptions above. If the animal on the Animals sheet does not exist on the Filter Criteria sheet it will leave a blank (so you will need to add this animal to the filter criteria sheet with a species).

You can add as many additional animals you like on either sheet. Just copy the formula down on the Animals sheet as more are added.

HTH
Nick