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

 

[elgringo56]

Oh, sorry, didn't see you question. I am using Office 2000

 

[brettvba]

you are probably using an older version than me it happens sometimes

you can record it yourself
select record macro
then click the cell you want to start in then goto edit find and type No Entry in the find box then
click the stop button
when you edit the code put
ActiveCell.Offset(0, 1).Select
at the bottom of it and it should work.
This message was edited by brettvba on 2002-04-29 18:08

 

[elgringo56]

OK, Brett. Here is what I have found. I went to help and looked up Cell.Find, then went to "Find Method. There it said that where you have SearchFormat, it should be "MatchByte. I changed it. No more compile error, but, the cell I end up operating on is cell BT4 + 1. Which is the Active Cell offset, not the found cell offset. Any ideas? By the way, you guys do one hell of a job. You are a god send to us poor novices.

 

[brettvba]

The code seems to change sometimes from version to version have you tried to record it yourself yet?

 

[elgringo56]

LOLOLOL, Oh, Brett, this is to funny. I think I have it. The problem is that there is an arguement called LookIn:=Formulas. I am deriving the NO ENTRY by a formula in each of those cells. If I chang LookIn to Values, I think it works. I will let you know. Thanks for all your help. I am so dumb at this stuff.

 

[dspl]

Are you trying to find "No Entry" or "NO ENTRY" ?

 

[elgringo56]

NO ENTRY, and I got it. You have to use the MatchByte and make sure your checking Values, not formulas. It worked. Thanks millions for your time. I learned a lot chaseing this one. Some day, if I live so long, I may actually learn how to do this stuff.

 

[brettvba]

no that should not matter just try to record it first it doesn't matter wether its caps or not.