How to tell the computer that "loop" belongs to "do until" in VBA?

I have a problem with the do until loop. The first test code below (code1) works well. However, if I put the part following the "Then" in a new row, as shown in the second code (as I plan to add more commands after the "Then" later), it returns the error message "Loop without do". The working code (code1) is:

Sub CopyValues()

Dim s As Integer
Dim r As Integer

Cells(2, 4).Value = "Before"
Cells(2, 5).Value = "After"

For r = 1 To 3
    s = 0

    Do
        s = s + 1
        If InStr(Cells(s, 2).Value, "ROI_" & r & "A") > 0 Then Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = Cells(s, 2).Value
    Loop Until InStr(Cells(s, 2).Value, "ROI_" & r & "A") > 0

Next r
End Sub

The not working code (code2) is this:

Sub CopyValues()

Dim s As Integer
Dim r As Integer

Cells(2, 4).Value = "Before"
Cells(2, 5).Value = "After"

For r = 1 To 3
    s = 0

    Do
        s = s + 1
        If InStr(Cells(s, 2).Value, "ROI_" & r & "A") > 0 Then
            Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = Cells(s, 2).Value
    Loop Until InStr(Cells(s, 2).Value, "ROI_" & r & "A") > 0

Next r
End Sub

Kind Regards Ferenc

Solution

Even if the error message you get is "loop without do", the error is actually about your If-statement that requires a separate line with End If after the command(s) you want to be executed within your If-statement.

When markers like this are missing, you'll often get an error message that does not represent what is the actual error, so keep an eye open for those missing ending elements (End If, Next, Loop, etc.) when debugging!

As a complement, here's a discussion about the one-line version vs multiple-line version of the If-statement:

1) When you only have one statement to execute when the condition is true:

you can use the multiple-line version which needs the End If at the end:

    If Condition Then
        Do_Something
    End If

you can use the one-line version of the If-statement which doesn't need the End If at the end:

    If Condition Then Do_Something

Which one to choose is a matter of preferences. In a case with only one statement to execute, it can seem appropriate to use the one-line version because it makes it neater.

However, the muliple-line version remains more flexible. For example, imagine that, down the road, you realize that you need your If-Statement to execute more statements, you'll have to switch back to the multiple-line version*. Furthermore, to incorporate Else or ElseIf in your code to execute something when the condition returns false, you'll also need the multiple line version** (More details here).

2) When you have more than one statement to execute when the condition is true:

You should use the multiple-line version like this:

    If Condition Then
        Do_Something
        Do_Another_Thing
    End If

You could, but should probably avoid to use the one-line version. But if you really want to, you'd have to use it in combination with the the colon (:) to separate your statements like this:

    If Condition Then Do_Something: Do_Another_Thing

In this case, the reason why I'm saying you should probably avoid using the one-line version is because it becomes quite hard to read and not everyone is familiar with the use of the colon(:) to put more than one statement on the same line (More details here)

Footnotes:
*: As explained in Part 2, you wouldn't have to switch absolutely, but that would be highly recommended.
**: You actually could write the Else on the same line using
If Condition Then Do_Something Else Do_Something_Else, but I would not encourage it for readability reasons.