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

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

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

Dim lastrow as Long, rng as Long, rng1 as Long
lastrow = Activesheet.Range("S28").Value + 1
set rng = Range("P2:P" & 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

--
Regards,
Tom Ogilvy


"ste mac" wrote in message
om...
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