Syntax for Do Until Loop

Question

What is the correct syntax for a Do Until Loop? This is my first try attempting and I am getting an error message that says "Loop without Do". Here is what I have:

Dim i As Long

Do

For i = 3 To 93

Sheets("Analysis").Select
Range("B" & i).Copy
Range("R3").PasteSpecial

Sheets("Analysis").Select
Range("Q3").Copy
Range("G" & i).PasteSpecial Paste:=xlPasteValues

Loop Until Sheets("Analysis").Range("L1") < 50

The code is just missing the Next before the Loop Until

L42
– L42

2014-02-27 15:21:19 +00:00
Commented Feb 27, 2014 at 15:21 — L42
– L42, Commented Feb 27, 2014 at 15:21

Community · Accepted Answer · 2017-05-23 11:50:25Z

1

Your code could use some real clean-up.

Dim i As Long

With Sheets("Analysis")
    Do Until .Range("L1").Value < 50
        For i = 3 To 93
            .Range("B" & i).Copy .Range("R3")
            .Range("Q3").Copy
            .Range("G" & i).PasteSpecial Paste:=xlPasteValues
        Next i
    Loop
End With

A couple of things. First, make sure that there's a mechanism somewhere that reduces the value in .Range("L1") to less than 50. Otherwise, this runs in an infinite loop (because you can't complete the condition to make it stop).

Second, make sure that your For loops are closed with Next. Either Next or Next x where x is the iterator variable you defined should work (in your case, x is i).

Third, read up on the differences of Do While-Loop, Do Until-Loop, Do-Loop While, and Do-Loop Until. Until and While are pretty self-explanatory. The placement of the condition is a bit of a difficulty for those beginning with loops and should be mastered without question. One really clear explanation can be found here.

Fourth, use With for sequential or simultaneous actions on a single object. Check this answer out for samples and explanations (check section (3)).

Hope this helps.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Feb 27, 2014 at 15:16

WGS

14.2k5 gold badges50 silver badges51 bronze badges

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

2 Comments

Dmitry Pavliv Over a year ago

actually, this line make no sence to me: .Range("B" & i).Copy .Range("R3") :) we could just write .Range("B93").Copy .Range("R3") without For i = 3 To 93 loop. The same thing here: .Range("G3:G93").Value=.Range("Q3").Value

WGS Over a year ago

I was inclined to make the same observation. The issue I have with this code is not so much the loop but the... unorthodox copying. Obviously, overwriting .Range(R3) over and over again escapes me. But even so, I see nothing that changes L1 as well. Perhaps OP is executing this inside a called sub and an outer sub decreases L1, etc. We never know until OP really comments on whether the above works or not... And I've got my guess it won't. Lol. :D If he just wants to transfer values, I believe your comment is the best as well. :)

Netloh · Accepted Answer · 2014-02-27 15:31:13Z

1

Although I can't figure out what you are trying to acomplish with you loop, the code should be changed to the following in order to make it "correct".

Dim i As Long
Do         
    For i = 3 To 93
        Sheets("Analysis").Select
        Range("B" & i).Copy
        Range("R3").PasteSpecial

        Sheets("Analysis").Select
        Range("Q3").Copy
        Range("G" & i).PasteSpecial Paste:=xlPasteValues
    Next i
Loop Until Sheets("Analysis").Range("L1") < 50

edited Feb 27, 2014 at 15:31

answered Feb 27, 2014 at 14:25

Netloh

4,3784 gold badges27 silver badges38 bronze badges

Collectives™ on Stack Overflow

Syntax for Do Until Loop

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related