Excel VBA - determine location of a keyword

[Sinobato]

Hello Excel/VBA gurus,

I'm trying to create a script that would give me the row value of
cell containing a unique key word I am looking. I am thinking of
options to do this:

1. Select the column, then go through each rows in the column from u
to down and compare if the value of the row contains my keyword. I
not, go to the next row until the keyword is found.

2. Select the column. Use a VBA function, of which I don't know, t
find the keyword.

Once the row number of the keyword is found, assign the row numbe
before this as my last "valid" row.

Can someone help me with the codes for the two options above?

Thanks in advance for your help!
Sinobat

 

[Tom Ogilvy]

Dim rng as Range
Dim lastRow as Long
set rng = Columns(1).Find("ABCD")
if not rng is nothing then
lastRow = rng.offset(-1,0).row
rng.offset(-1,0).Select
else
msgbox "ABCD not found"
End if

no reason to pursue method 1.

 

[Sinobato]

Tom, thanks for your help!

Regards,
Sinobat