Do Until myString

[LEXCERM]

Hi,

I want to be able to specify the statement directly after "Do Until" based on two possible conditions, i.e.....


If ActiveCell.Row > NewStatement Then
NewStatementLoop = "ActiveCell = """
Else
NewStatementLoop = "ActiveCell.Row = " & NewStatement
End If

Do Until NewStatementLoop


I keep getting Type Mismatch and general debug errors.

Can this be done?

Many thanks in advance.

 

[slinky]

Can you post the whole routine?

 

[LEXCERM]

Thanks for replying slinky.

Can't post all of it, but this is the general gist:-


NewStatement = Cells.Find(What:="Test Cell").Row

If ActiveCell.Row < NewStatement Then
NewStatementLoop = "ActiveCell = """
Else
NewStatementLoop = "ActiveCell.Row = " & NewStatement
End If

Do Until NewStatementLoop
''''>>> loads of code and stuff
Loop


Code errors at the line "Do Until NewStatementLoop". I simply want to define what statement to use.
Thanks!

 

[slinky]

You're suggesting that something runs until NewStatementLoop, what is NewStatementLoop?

Does it ever become True/False? Otherwise you're going to be better off with a

do until activecell.row = NewStatementLoop

or something similar..

 

[LEXCERM]

NewStatementLoop will either be: "ActiveCell = """, or "ActiveCell.Row = NewStatement". I want either of those two statements to follow "Do Until".

I am evaluating whether the string "Test Cell" is above or below the active cell in my routine.

 

[slinky]

You're going to need to give me more to work with here I'm afraid.

 

[LEXCERM]

No worries and thanks for your time. There are other ways I can handle this, but I was just curious to see if we could use a variable string directly after DO UNTIL.

 

[slinky]

You can, but you can't simply say Do Until String . It needs to be Do Until String = Something

ETA : Have a nose at this -> http://www.ozgrid.com/VBA/do-until-loop.htm

 

[LEXCERM]

Thanks for the link. I get the concept of the loop syntax, which is what I am trying to do here. So, it'll either be:-


Do Until ActiveCell = ""
...or...
Do Until ActiveCell.Row = 98 (which will be the NewStatementRow identifier)


At this point in the code, the cursor position is being evaluated.

Maybe I'm not explaining myself proeprly, which I apologise for. It's bright and sunny outside and that's where I wanna be!!!

 

[slinky]

I see that sun shining here too!

This works for me, but I'm not sure if it'll fully comply with what you need...

Sub test()
NewStatement = Cells.Find(What:="Test Cell").Row
Do Until ActiveCell.Row = NewStatement
ActiveCell.Offset(1, 0).Select
Loop
End Sub

The loop exits at the point that the Activecell.Row value = NewStatement

So, it would make sense to encapsulate that in the IF statement verifying whether the value of NewStatement is greater than the row of the current cell.