Using "Do Until" in conjunction with "And, Or" not looking for 2nd condition

Question

I am running a Do Until loop that is lowering the value in target cells, starting at .99, (x=.99) and then using those values to complete a calculation. I need this loop to stop if 1 of 2 conditions is met.

Calculated value is within 10% of the target. OR
x = .75

If the calculation doesn't match the target, the loop will lower x by .005 and try again. But I want .75 to be the lower limit of x.

Do Until 

(Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Range("J23").Value * -1 >= Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Range("U28").Value * 0.9 And Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Range("J23").Value * -1 <= Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Range("U28").Value * 1.1) Or (x = 0.75)

    Deal_ID = VBA.Right(Workbooks("Weekly Option Update (Master).xlsm").Sheets("GDD Group").Cells(i, "G").Value, 7)
    Sheets("Correlation").Range("E7").Value = x
    Workbooks("Group 4 Correlation Solver").Sheets("Correlation").Range("F8").Value = x
    Workbooks("Group 4 Correlation Solver").Sheets("Correlation").Range("C9").Value = x
    Workbooks("Group 4 Correlation Solver").Sheets("Correlation").Range("D10").Value = x
    Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Calculate
    x = x - 0.005
    Workbooks("Weekly Option Update (Master).xlsm").Sheets("GDD Group").Cells(i, "H") = x + 0.005

Loop

My code might not be the most efficient but it does currently run. The issue is that it is not looking at the 2nd condition of the lower limit of x. It simply keeps going until the calculated value is within 10% of the target.

I apologize in advance for the code format. The block right beneath "Do Until" is the code in question with the "and" and "or".

Where do you initiate `x`? How do you know it is reaching `.75`? — dwirony, Jun 04 '19 at 18:58
The line right before Do Until I set x=.99. Sorry for not including that. At the end of the loop I set x = x - .005. So when the loop runs again x=.985. The first line of should be checking if x=.75 and if it is, then exit the loop. — Brice Hatcher, Jun 04 '19 at 19:01
Try `x <= 0.75` instead; I'm suspecting floating-point rounding is the issue here. — rskar, Jun 04 '19 at 19:05
Obligatory link: [Is floating point math broken?](https://stackoverflow.com/q/588004/11683) — GSerg, Jun 04 '19 at 19:18

Mathieu Guindon · Accepted Answer · 2019-06-04T20:06:08.443

Boy that's a mouthful! Extract local variables, there's no need to repeatedly dereference the same objects over and over and over every time!

Local variables make things much easier to debug, too.

Dim solverBook As Workbook
Set solverBook = Application.Workbooks("Group 4 Correlation Solver")

Dim weeklyOptionBook As Workbook
Set weeklyOptionBook = Application.Workbooks("Weekly Option Update (Master).xlsm")

Dim gddGroupSheet As Worksheet
Set gddGroupSheet = weeklyOptionBook.Worksheets("GDD Group")

Dim structureSheet As Worksheet
Set structureSheet = solverBook.Worksheets("STRUCTURETOOL")

Dim currentValue As Double
currentValue = structureSheet.Range("J23").Value ' CAUTION: possible type mismatch here

Dim targetValue As Double
targetValue = structureSheet.Range("U28").Value ' CAUTION: possible type mismatch here

Const threshold As Double = 0.1
Const limit As Double = 0.75

Dim correlationSheet As Worksheet
Set correlationSheet = solverBook.Worksheets("Correlation")

Do Until (currentValue * -1 >= targetValue * (1 - threshold) _
  And currentValue * -1 <= targetValue * (1 + threshold)) _
  Or x <= limit

    Deal_Id = Right$(gddGroupSheet.Cells(i, "G").Value, 7)
    correlationSheet.Range("E7,F8,C9,D10").Value = x
    structureSheet.Calculate

    gddGroupSheet.Cells(i, "H") = x
    x = x - 0.005

    currentValue = structureSheet.Range("J23").Value ' CAUTION: possible type mismatch here
    targetValue = structureSheet.Range("U28").Value ' CAUTION: possible type mismatch here
Loop

Don't use = when dealing with floating points. Or x <= limit is probably the solution to your immediate problem.

Maybe `currentValue = structureSheet.Range("J23").Value` needs to happen inside the loop too? It may change value whenever cells E7,F8,C9,D10 are changed to the new x. — rskar, Jun 04 '19 at 20:02
@rskar right, after the recalc. That said I'm not sure `targetValue` needs to be recalculated - depends what's in OP's actual worksheet. — Mathieu Guindon, Jun 04 '19 at 20:06

score 0 · Answer 2 · answered Jun 04 '19 at 19:01

I would just use an If to test the second condition:

x = 1

Do Until x = 0.75

    if Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Range("J23").Value * -1 >= Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Range("U28").Value * 0.9 And Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Range("J23").Value * -1 <= Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Range("U28").Value * 1.1 then exit do

    Deal_ID = VBA.Right(Workbooks("Weekly Option Update (Master).xlsm").Sheets("GDD Group").Cells(i, "G").Value, 7)
    Sheets("Correlation").Range("E7").Value = x
    Workbooks("Group 4 Correlation Solver").Sheets("Correlation").Range("F8").Value = x
    Workbooks("Group 4 Correlation Solver").Sheets("Correlation").Range("C9").Value = x
    Workbooks("Group 4 Correlation Solver").Sheets("Correlation").Range("D10").Value = x
    Workbooks("Group 4 Correlation Solver").Sheets("STRUCTURETOOL").Calculate
    x = x - 0.005
    Workbooks("Weekly Option Update (Master).xlsm").Sheets("GDD Group").Cells(i, "H") = x + 0.005

Loop

Using "Do Until" in conjunction with "And, Or" not looking for 2nd condition

2 Answers2