Limit search range then loop thru' each cell...a bit stuck...

[ste mac]

Hello there...

I have this code so far..

Sub findnumber5()
Sheets("Data sheet").Range("P2").Select
Do Until ActiveCell.Value = ActiveSheet.Range("R20").Value
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Range("S13").Value = ActiveCell.Row - 1
'the range needs to be P2:activesheet.value("S28")+1
End Sub

I need to find the very first occurence of the number 5, but the code
will
search all 65000 rows whereas I only need to seach: thru
P2:activesheet.value"S28")+1 ... and what if the number 5 is not
there?
it will need to exit..any help would be greatly appreciated...

ste

 

[Tom Ogilvy]

Dim lastrow as Long, rng as Long, rng1 as Long
lastrow = Activesheet.Range("S28").Value + 1
set rng = Range("P2" & LastRow)
set rng1 = rng.Find(Range("R20").Value)
if not rng1 is nothing then
msgbox "Found at " & rng1.Address
else
msgbox "Not found"
End if