loop without do - Sorry if this is a repeat of the ultimate noob question but...

dianehc's picture

But essentially just starting off with basic coding and trying, I'm trying to work out the different application possibilities in conditional branching between For/Next and Do until/Loop combinations.

To that end, I tried two small macros to compare the results based on a "Sub branchingtest()" from a VB_intro pdf by Michael Brydon.

Sub tutorial_329_fornext
Dim i As Integer
Dim s As String
Dim acell As Range

i = 1
For Each acell In Range("A1:A16")

If acell.Value <> "" Then
Debug.Print acell.Offset(0, 1).Value
Else
Debug.Print "last row checked " & i
End If
i = i + 1

Next acell
End Sub

'this worked the way I hoped it would but it returned results for two empty rows until it reached the end of the range

'instead of just one; however, I don't forsee this as being a problem if it only returns two empty rows, but ...

'as the anticipated goal will be to check for class sections and return the student's name in the next column, but if it returned similar reports for the other out of section students, this could be a problem in correlating the

'class list section and student list information to create class rosters

Here is the bit I have a question about:
' I keep getting a compile error for having a "loop without a do", but I do have a do in line

Sub tutorial_329_dountil()

Dim i As Integer
Dim s As String
Dim acell As Range
s = "Row number "
i = 0

Do Until acell.Value = "" '******* here is my DO line*****

i = i + 1

For Each acell In Range("A1:A16")
If acell.Value <> "" Then
Debug.Print acell.Offset(0, 1).Value
Else
Debug.Print "last row checked " & i
End If

Loop'replacing Next acell

End Sub

What am I doing wrong?????

Loop to Last Used Cell in Column

You can count the rows in Column A. I see right now you just loop through the range until the Cell is empty.
What is Dim s as string for?

Try this instead.

Sub LoopToLastUsedCell()

Dim Rws As Long, Rng As Range, c As Range

Rws = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, 1), Cells(Rws, 1))

For Each c In Rng.Cells

Debug.Print c.Offset(0, 1)

Next c

End Sub

Checkout my "Range Selection Codes"
http://www.davesexcel.com/rangeselectioncodes.htm

Dave

That was me

I answered the above question before realizing how to sign in.

Thank you.

Thank you . I had a similar glitch when I posted the image to my profile, I thought it would come through as a 100*100 block instead of a 480 or larger. Think I've trimmed it down now, but haven't posted another question again to double check.