marcro to assign a variable to the row number of a cell contains a particular word

peteryau
peteryau used Ask the Experts™
on
HI
I want to assign an variable  in excel marco which returns  the row number of a cell in Colume A with the word "find_me".  The word "find_me only exist one time in column A.
How can I do it?

Thanks
Peter
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
Does the cell consist only of the word "find_me", or does it just contain the word, like "can you find_me in this cell".  Are you worried about upper or lower case?

activesheet.range("A:A").find("find_me").row
A simple solution

Dim foundRow As Long

    Cells.Find("find_me", Range("A1"), , xlByColumns).Activate

    foundRow = ActiveCell.Row

    MsgBox foundRow

Open in new window

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Although ssagibh's solution is simpler and better.  I always forget the really easy methods!
peteryauManager

Author

Commented:
Yes, the word find_me is case sensitive and only of the word "find_me"
peteryauManager

Author

Commented:
how to show the row number  which has exactly the value "find_me"?
ssagibh's solution shows the row number of "find_me_too"  which occurs before "find_me"
activesheet.range("A:A").find("find_me",,,xlwhole).row
activesheet.range("A:A").find("find_me",,,xlwhole,,,true).row

will also match the case

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial