If the word is not in the column, then FirstRow and LastRow will be set to
zero.

I need to qualify the above statement. If your code calculates FirstRow and
LastRow within a loop, then these variables will retain the value they had
during the previous iteration of the loop. To avoid that problem (again,
this is ONLY if these variables are calculated within a loop), add these two
statements...

FirstRow = 0
LastRow = 0

immediately after the On Error GoTo NotThere statement.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Without the benefit of seeing your existing code, the best I can do is
give you a framework to follow...

' <<Your "lead-in" code goes here
'
Word = "cat"
On Error GoTo NotThere
FirstRow = Range("A:A").Find(Word, LastCell, , , , xlNext).Row
LastRow = Range("A:A").Find(Word, LastCell, , , , xlPrevious).Row
NotThe
On Error GoTo 0
'
' <<Rest of your code goes here

If the word is not in the column, then FirstRow and LastRow will be set to
zero.

--
Rick (MVP - Excel)


"Sunil Patel" wrote in message
...
hi there,

When searching column "a", how can i return the row numbers of the first
and last cell in which there is a particular word

dog
cat
cat
cat
fish

i want to return firstrow%=2 and lastrow%=4 if looking for "cat"

Using excel 2000

thanks