VBA - simple one. How do I stop a loop when a column is reached?

[RockandGrohl]

Hi all,

We all know the traditional "Do until cells(activecell.row, "A").value = "" "

Meaning, stop when the first blank row is reached.

I'm trying to loop through a range of columns, offsetting every 11 columns, and I want to stop when either the active cell is blank or the column reaches DD


I'm really rusty with VBA at the moment and I can't remember how to do anything, so I'd like to avoid anything more complex for the moment.

I would expect the following to work:

Do until activecell.value = "" or activecell.column = "DD" 

--doing stuff here--

activecell.offset(0,11).activate
Loop

Instead I get a type mismatch. Any ideas? Thanks.

 

[Norie]

Try this.

Do Until ActiveCell.Value = "" Or ActiveCell.Column >= Columns("DD").Column

    '--doing stuff here--

    ActiveCell.Offset(0, 11).Activate

Loop

 

[RockandGrohl]

Jesus that was easy. Thanks a million.

 

[RockandGrohl]

Now how the hell do I paste to a cell below the active row?

Why has my brain just deleted VBA...

    ActiveCell.Offset(1).EntireRow.Insert
    Range("A" & ActiveCell.Row, "O" & ActiveCell.Row).Copy
    Range("A" & ActiveCell.Row + 1).Paste

 

[Yongle]

One way

Do Until ActiveCell.Value = ""
    If ActiveCell.Column >= Range("DD1").Column Then Exit Do
    ActiveCell.Offset(0, 11).Activate
    'etc
Loop

 

[Yongle]

Now how do I paste to a cell below the active row?

are you inserting cells (ie moving the rows below down) or pasting into those cells