"Do until" loop exits the macro before the "for each" loop ends

Question

For each row of my worksheet, I'm trying to browse cells. I'll stop when I encounter an empty cell. Then I want to go to the following row.

In order to do this I'm using a "Do until" loop with a "IsEmpty" condition. This "Do until" loop is inside a "For each" loop. The "For each" loop is used to go from one range to another in the first column. From A1 to A2, to A3 etc.

I manage to browse the first line successfully. But I can't manage to get down to the second row, and the following ones. I'm assuming that the "Do until" loop exits the macro without letting Excel read the "Next rng" statement...

My macro exits, and the active cell is the first empty cell of the first row. (we should now go to the second row, not stop)

Here is my code :

Sub label_clusters()

Dim rng As Range, iCounter As Integer
iCounter = 1

For Each rng In Feuil1.Range("A1:A24")

If ActiveCell.Column = 1 Then
Sheets("Feuil2").Cells(iCounter, 1) = rng.Value
Sheets("Feuil2").Cells(iCounter, 2) = rng.Offset(0, 1).Value
iCounter = iCounter + 1
ActiveCell.Offset(0, 2).Select
End If

Do Until IsEmpty(ActiveCell.Value) = True

If ActiveCell.Column <> 1 Then
Sheets("Feuil2").Cells(iCounter, 1) = rng.Value
Sheets("Feuil2").Cells(iCounter, 2) = ActiveCell.Value
iCounter = iCounter + 1
ActiveCell.Offset(0, 1).Select
End If

Loop

Next rng

End Sub

I'm obviously doing something wrong, but after googling again and againd I'm now short on ideas. Any suugestion will do =) Thanks a lot for your help.

Andrew Cooper · Accepted Answer · 2013-03-20 04:59:11Z

1

The problem is with this line:

If ActiveCell.Column = 1 Then

After you finish the first row, ActiveCell is the first empty cell, and ActiveCell.Column is > 1. So for each iteration of the for each loop after the first, the first code block gets skipped, and the ActiveCell never gets updated to the first cell of the next row.

looking at your code, both if statements seem to be superfluous. I think the code will perform correctly if you delete both if and end if lines.

answered Mar 20, 2013 at 4:59

Andrew Cooper

32.7k5 gold badges86 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicolas Over a year ago

Thanks a lot Andrew, with your answer it took me less than one minute to figure this out. I see my mistake clearly now. Droping the if didn't do it. But, having understood the problem. I added "rng.Select" at the top. So that after I finish one row I start by uptading my active cell.

Collectives™ on Stack Overflow

"Do until" loop exits the macro before the "for each" loop ends

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related