0

What I want this part of the code to do is if the value of cell H83 is greater than 22 then I want the code to increase the value of cell R14 which is 0.7 by an increment of 0.01 until either R14 reaches 0.75 or until H83 is less than 22. I have tried:

For j = 0.69 To 0.74 w = j + 0.01 If Range("h83") > 22 Then Range("r14").Value = w If 21 < Range("h83") < 23 Then Exit For End If Next j

This doesn't work and right now I have it so it increases it by 0.01 only once (part in asterisks), full code:

Sub C_CreateTestResultTableV2() Application.ScreenUpdating = True '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 4, 1 To c) For i = 1 To c 'checks to see if t_air_in > 22 If vInputs(1, i) > 22 And vInputs(3, i) < 70 Then 'set values Range("j18") = vInputs(1, i) Range("n14") = vInputs(3, i) Range("r16") = vInputs(5, i) 'checks to see if t_air_out = 22 and changes t_wat_in and m_wat_in accordingly If Range("h83") > 22 Then Range("r16").Value = Range("r16").Value - 3 End If *If Range("h83") > 22 Then Range("r14").Value = Range("r14").Value + 0.01 End If* 'copy output values into RESULTS vResults(1, i) = Range("h83") vResults(2, i) = Range("k83") vResults(3, i) = Range("z14") vResults(4, i) = Range("r15") End If 'resets values Range("r16").Value = 13 Range("r14").Value = 0.7 Next i Range("b96").Resize(4, c) = vResults Application.ScreenUpdating = True End Sub
Improve this question
2
  • 1
    For j = 0.69 To 0.74 Step 0.01 Commented Feb 24, 2019 at 17:06
  • Ah my bad. That fixed it, thanks for your help! Commented Feb 24, 2019 at 17:16

1 Answer 1

Reset to default
0

If you want something to happen until a certain condition is met, but you don't know exactly when that happens, you need a Do-Loop:

Do While Range("H83").Value < 23 Range("R14").Value = Range("R14").value + 0.01 Range("H83").value = Range("H83").value + x 'If you don't do something with Range("H83"), the loop will go on forever Loop

Edit: From someone who made this mistake more than willing to admit: If you create a do-loop, please make sure that you don't create an infinite loop, because the chance is high that VBA won't respond anymore.

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

1 Comment

Yeah happens too often to myself as well haha. Thanks for the answer!