For i - 1 to Something loop

[elgringo56]

I have the following routine. In it I am looking for the first cell that contains the word NO ENTRY. The first time I find this value, I would like to leave this routine without doing the entire 10000 passes. Is there a way to do that?

For i = 1 To 10000
Select Case Range("BT3").Offset((i - 1), 0).Value
case Is = "ENTRY"
Range("BQ3").Offset((i - 1), 0).Select
Case Else
Range("BQ3").Offset((i - 1), 0).Select
Selection.ClearContents
End Select
Next i

[brettvba]

if you are just trying to find "No Entry" you could just do a find or is there more that your not telling me>?

[elgringo56]

I need to find the first NO ENTRY and then use the offset ((1-i... at that point to modify the cell next to the point that I find the entry.

[elgringo56]

In other words, once I find that point I want to do an operation at that point then leave the loop, not go thru all 10000 locations.

[brettvba]

you could try
Cells.Find(What:="No Entry", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
to find 'No Entry' then offset (move to the next column) and modify your entry.

any help?

[elgringo56]

Wow, Brett, That one is a little over my head. I am a total novice and thought finding the i = loop was an accomplishment. I can play with what you gave me a bit and see if I can make it work. How do I specify the starting point to begin looking in it?

[brettvba]

it will just find the first one on the page top to bottom so you could specify a start location of
range("a1").select if you wish to start at the top.

[elgringo56]

I selected Cell BT3 then put in your command line. when I ran it, it stopped with Compile error, named arguement not found. The SearchFormat:= was highlighted. ?????

[brettvba]

On 2002-04-29 16:35, brettvba wrote:
you could try
Cells.Find(What:="No Entry", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
to find 'No Entry' then offset (move to the next column) and modify your entry.

any help?

Range("BT3").Select
Cells.Find(What:="No Entry", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select

should work what version are you using



[ This Message was edited by: brettvba on 2002-04-29 17:15 ]

[elgringo56]

I'm sorry, Brett, but I get the same thing. Compile error, Named arguement not found. SearchFormat:= highlighted