Do Loop or use End iF for search string

[RGreen]

50 colums (BIC) 1500 rows (500 to 2000)
On the cs772nd row, cs797th row, cs822nd row and so on, in each column I
have the word "stop".
The data is already stored or I am storing data into these colums:

Depending on where I am storing data, I would like to search that column for
the word stop, and I want my active cell to be just above the word stop.

SO If I am in CU775 (Activecell) than I would like to search that column CU
for the word stop down to row 797 and my active cell would be CU796.

What would be the best way for me to approach this with not too much coding
as I need to accomodate this to another marco already in place.

Thanks,
Rick

 

[Stefi]

Try this:

Cells(Columns(ActiveCell.Column).Find(What:="stop", _
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row - 1, ActiveCell.Column).Select

You may have to apply On Error for handling cases when "stop" is not found!

Regards,
Stefi

„RGreen†ezt írta:

 

[Jacob Skaria]

Try the below macro

Sub Macro()
Dim varFound As Variant
Set varFound = Columns(ActiveCell.Column).Find("Stop")
If Not varfound Is Nothing Then varfound.Offset(-1, 0).Activate
End Sub


If this post helps click Yes

 

[Jacob Skaria]

Oops...correction..

Sub Macro()
Dim varFound As Variant
Set varFound = Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp)).Find("Stop")
If Not varFound Is Nothing Then varFound.Offset(-1, 0).Activate
End Sub

If this post helps click Yes

 

[RGreen]

Hey Jacob, this did the trick

Stefi, haven't tried it yet, but thanks for the posting.

Rick.