Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
stuck in an error loop | Excel Discussion (Misc queries) | |||
Stuck in a Sub Loop | Excel Worksheet Functions | |||
Excel with game is stuck in a loop | Excel Discussion (Misc queries) | |||
Stuck in a loop | Excel Discussion (Misc queries) | |||
Excel 97 stuck in calculation loop - maximum cells problem? | Excel Programming |