Do Until Loop, If Then VBA Excel

Question

UPDATE: I got the code to do exactly what I wanted it to do (pasted below), although some may probably disagree with my method (and I'll take feedback because I am 100% new to this).

I am trying to create a simple VBA code to run a loop with an if then statement -and if the statement is true then stop the loop. If the statement is never true until the end, then stop the loop when cell is IsEmpty().

This is what I have so far. It is obviously incorrect and it is not running, but I hope this attempt will help you grasp an idea of what I'm trying to do.

Sub runoutDateLoop()
r = 8
Do Until IsEmpty(Cells(r, 1))
c = 24
Do Until Cells(r, c - 1).Value < 1 = True
 If Cells(r, c).Value < 1 Then
 Cells(r, 18).Value = Cells(7, c)
 Else
 Cells(r, 18).Value = Cells(7, c)
 End If
 c = c + 1
Loop
r = r + 1
Loop
End Sub

I am trying to create a loop until the if statement is true. The if then statement is simply- if the number is a negative number, then we want cell r,18 to copy to cell 7,c. If not, then the loop occurs until there is a negative number OR if there is never a negative number, then until the cell is empty. I want this to also have a double Do Until loop with every row.

Anyone have any ideas please??

Thank you!

Unrelated, but `IsEmpty` may not be the function you're looking for. It will return false for a formula that results in an "empty" cell, for example `=""`. — Comintern, Aug 06 '18 at 23:41
Instead of a `Do Until` loop, you might want to consider [finding the last row with data](https://stackoverflow.com/q/13686801/4088852) and then using a `For` loop instead. — Comintern, Aug 06 '18 at 23:49
`If Cells(r, c).Value < 1 Then` will never execute this way because `Do Until Cells(r, c).Value < 1 = True` will stop the `DO` loop at that condition. Also, not sure where you want the `ELSE` statement, but it should be within an `IF` : `END IF` structure. — Rey Juna, Aug 06 '18 at 23:58
Did you update your code that you said you got to work? Also, your image doesn't seem to apply. — Rey Juna, Aug 06 '18 at 23:58
@Samantha As you rightly stated, there are several errors in vour code. Try compiling the VBA project in the Debug menu in the VBE. — Miqi180, Aug 07 '18 at 00:00

score 1 · Answer 1 · answered Aug 06 '18 at 23:58

To exit a Do loop early, use Exit Do. Since you have two nested loops, you will need a flag to exit the outer loop.

Sub RunoutDateLoop()
    Dim r As Long, c As Long
    Dim Found As Boolean
    r = 8
    Do Until IsEmpty(Cells(r, 1))
        c = 24
        Do Until IsEmpty(Cells(r, c))
            If Cells(r, c).Value < 1 Then
                Cells(r, 18).Value = Cells(7, c)
                Found = True
                Exit Do 'Exit the inner do
            End If
            c = c + 1
        Loop
        If Found Then Exit Do 'exit the outer do
        r = r + 1
    Loop
End Sub

Note: I make no comment on the logic within the loops, it's a bit unclear what your data is, and the exact logic you require (eg if the number is a negative number vs If Cells(r, c).Value < 1)

Hi, May I ask what the purpose of "Dim r As Long, c As Long Dim Found As Boolean" is? — Samantha, Aug 07 '18 at 00:22
Re the `Dim`'s they declare the variables as the correct data types. [See here for further reading](https://stackoverflow.com/q/24378383/445425). You should use `Option Explicit` to force yourself to do this — chris neilsen, Aug 07 '18 at 00:26

score 1 · Answer 2 · answered Aug 07 '18 at 00:20

with help everyone's comments, this is what I have and works exactly what I'd like it to do. It may be a little confusing because my data is very convoluted and hard to understand without actually seeing the spreadsheet.

Sub RunoutDateLoop()
Dim r As Long, c As Long
Dim Found As Boolean
r = 8
Do Until IsEmpty(Cells(r, 1))
    c = 24
    Do Until IsEmpty(Cells(r, c))
        If Cells(r, c).Value < 1 Then
            Cells(r, 18).Value = Cells(7, c)
            Found = True
            Exit Do 'Exit the inner do
                Else
                Cells(r, 18).Value = Cells(7, c)
        End If
        c = c + 1
    Loop
    r = r + 1
Loop
End Sub

I think it is good enough except you need to take on Chris' advice with being explicit and properly referencing your objects. That improves accuracy and eliminates most of the *runtime errors*. — L42, Aug 07 '18 at 01:15

Do Until Loop, If Then VBA Excel

2 Answers2