Do Until loop to get value of a cell greater than a certain number

Question

I want the code to read the value of 'vResults(1, i)' and if it is greater than 21, I want it to increase the value of 'vInputs(5, i)' by 1 and keep doing that until 'vResults(1, i)' is greater than 21.

Doesn't give out any errors, but Excel just crashes.

Sub CreateTestResultTableV2()
    Application.ScreenUpdating = False 'helps the code run faster

    Dim vInputs, vResults()
    Dim c As Integer, i As Integer

    'create INPUTS 
    c = Range("b5").End(xlToRight).Column
    vInputs = Range("b5", Cells(9, c))
    'determine last value in the column
    c = UBound(vInputs, 2)

    'create RESULTS 
    ReDim vResults(1 To 3, 1 To c)

    For i = 1 To c
        If vInputs(1, i) > 22 Then
            'set values
            Range("j16") = vInputs(1, i)
            Range("n12") = vInputs(3, i)
            Range("r14") = vInputs(5, i)

            'copy output values into RESULTS 
            vResults(1, i) = Range("h41")
            vResults(2, i) = Range("k41")
            vResults(3, i) = Range("z14")

            Do Until vResults(1, i) > 21
                vInputs(5, i).Value = vInputs(5, i).Value + 1
            Loop
        End If
    Next i

    Range("c47").Resize(3, c) = vResults
    Application.ScreenUpdating = True
End Sub

`Do Until vResults(1, i) > 21` ... `Loop` - nothing inside this loop is going to change `vResults(1, i)` so will just go around and around endlessly. — CLR, Feb 08 '19 at 14:51

Sirmyself · Accepted Answer · 2019-02-18T16:01:36.530

3

The do until loop repeats its instructions until the tested condition becomes true. In your case, yours has a condition that will never change:

Do Until vResults(1, i) > 21
    vInputs(5, i).Value = vInputs(5, i).Value + 1
Loop

You will enter the loop until vResults(1, i) is greater than 21, but since the value never changes, the loop is never going to end (AKA : infinite loop). Windows will eventually say that excel is not responding and will ask you if you want to end the process to get out of the loop, which may look like the app has crashed.

Either change the value of vResults(1, i) or change the condition to check something else that will change in the loop (probably vInputs(5, i)).

edited Feb 18 '19 at 16:01

answered Feb 08 '19 at 14:55

Sirmyself

1,464
1
14
29

Thanks for you response. I put 'Do Until vResults(1, i) = 21' but now it gives an object required error? – z-siddiqi Feb 08 '19 at 15:06
Sorry, I misread your comment... You probably have to check if you are still within the range of cells when you check that loop – Sirmyself Feb 08 '19 at 15:09
If you just want to change the cells around a certain cell, you can use the `Offset` property. this allows you to not care about range limits (only the sheet's limits) – Sirmyself Feb 08 '19 at 15:14
check the answer int [this post](https://stackoverflow.com/questions/19048601/object-required-error-in-excel-vba) this might be the source of the error – Sirmyself Feb 08 '19 at 15:16

Do Until loop to get value of a cell greater than a certain number

1 Answers1