PDA

View Full Version : Solved: Loop Until Blank or situation



marco_wales
04-07-2009, 06:52 AM
I have this code and I want it to do as below but I also want it to stop if the cell is blank. CValue has already been predetermined

Is there a way to include both do untils in one?

so stop looping if cell is blank or if the cell is less than or equal to CValue

Any ideas please?


ActiveCell.Offset(-1, 0).Select
Do Until Selection <= CValue
ActiveCell.Offset(-1, 0).Select
Loop

Kenneth Hobs
04-07-2009, 07:03 AM
Welcome to the forum!
Dim r as Range
set r = ActiveCell.Offset(-1, 0)
Do Until r <= CValue or IsEmpty(r)
Set r = ActiveCell.Offset(-1, 0)
Loop

marco_wales
04-07-2009, 07:28 AM
Thank you for the post however after trying out the code it doesn't work.

I need it to select the cell that it ends in and I can't get the code to work as everytime I run your code it freezes the page and I have to exit excel

Bob Phillips
04-07-2009, 08:24 AM
Dim r As Range
Dim i As Long
Set r = ActiveCell.Offset(-1 + i, 0)
Do Until r.Value <= CValue Or IsEmpty(r)
i = i + 1
Set r = ActiveCell.Offset(-1 + i, 0)
Loop
ActiveCell.Offset(-2 + i, 0).Select

Kenneth Hobs
04-07-2009, 08:29 AM
I am not sure what you are trying to do. Go from the activecell or the the one below it? Then go down I suspect. Use -1 if you want to go up.

Making a guess:
Dim r As Range
Set r = ActiveCell.Offset(-1, 0)
Do Until r <= CValue Or IsEmpty(r)
debug.print r.address, r.value 'Replace with that you want to do with r
Set r = r.Offset(1, 0)
Loop

mdmackillop
04-07-2009, 08:41 AM
Do
i = i + 1
Loop Until ActiveCell.Offset(-i) = "" Or ActiveCell.Offset(-i) <= cvalue
ActiveCell.Offset(-i).Select

marco_wales
04-08-2009, 04:55 AM
Thank you all for your help it was much appreciated.

mdmackillop - your macro was the one I went with, did what I wanted with minimum fuss!

Kenneth Hobs
04-08-2009, 05:24 AM
Select is usally not the best route. Most people starting out use it because that is what the macro recorder returns. If you must use it, turn off the settings so that your code will execute it faster. Of course if you have just a few cells to iterate, it won't matter much.

So, if speed is important to you and you must use Select, try the routines that I posted in this KB entry. http://vbaexpress.com/kb/getarticle.php?kb_id=1035 (http://vbaexpress.com/forum/../kb/getarticle.php?kb_id=1035)

If you want to go beyond the macro recorder, see example 1 at this site. http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/