Solved: Loop Until Blank or situation

[marco_wales]

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?


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

[Kenneth Hobs]

Welcome to the forum!
[VBA]Dim r as Range
set r = ActiveCell.Offset(-1, 0)
Do Until r <= CValue or IsEmpty(r)
Set r = ActiveCell.Offset(-1, 0)
Loop[/VBA]

[marco_wales]

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]

[vba]

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
[/vba]

[Kenneth Hobs]

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:
[vba]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 [/vba]

[mdmackillop]

[VBA]Do
i = i + 1
Loop Until ActiveCell.Offset(-i) = "" Or ActiveCell.Offset(-i) <= cvalue
ActiveCell.Offset(-i).Select
[/VBA]

[marco_wales]

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]

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

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