use Find without hardcoded value but a value from a cell
 

Hi

I am trying to search within Excel VBA a value in a sheet dependant on
the entry in another cell. But not sure how to put a paramater as the
What part of the Find.

Can anyone help please?

i.e if cell A1 is Cat then I would search for Cat in the other sheet.

Dim Animalname

AnimalName = Combo1.Value

Sheets("Animals").Select


Cells.Find(What:=Animalname,After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

use Find without hardcoded value but a value from a cell
 

I think what I may be better doing is using a loop of some sort. I
want to search a named range for the text in a combobox. So Combo1
says "cat" sheet Animals has a list of animals in a named range, say
AnimalType, and i want to search though this range and then stop when
"cat" or whatever animal has been found.

StartPoint is a named range startpoint on "Animals" sheet (say $D$10)

So sort of loop the following:

Worksheets("Animals").Range("StartPoint").Select
If ActiveCell.Value = Animalname Then
ActiveCell.Select
'and somehow exit the loop
Else: ActiveCell.Offset(0, 1).Select

then loop back.

I dont know when it would stop if there was no "cat" listed either.

Any help would be brilliant.

Cheers

Rich