1

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
halfer
  • 19,824
  • 17
  • 99
  • 186
Chris2015
  • 1,030
  • 7
  • 28
  • 42

2 Answers2

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
Netloh
  • 4,338
  • 4
  • 25
  • 38
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.

Community
  • 1
  • 1
WGS
  • 13,969
  • 4
  • 48
  • 51
  • 1
    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` – Dmitry Pavliv Feb 27 '14 at 15:24
  • 1
    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. :) – WGS Feb 27 '14 at 15:28