For i - 1 to Something loop

elgringo56

Well-known Member
Joined
Apr 15, 2002
Messages
869
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
if you are just trying to find "No Entry" you could just do a find or is there more that your not telling me>?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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. ?????
 
Upvote 0
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
 
Upvote 0
I'm sorry, Brett, but I get the same thing. Compile error, Named arguement not found. SearchFormat:= highlighted
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top