excel - 将 "Do Until"与 "And, Or"结合使用，不寻找第二个条件

Question

我正在运行一个 Do Until 循环，该循环降低了目标单元格中​​的值，从 0.99 (x=.99) 开始，然后使用这些值来完成计算。如果满足 2 个条件中的 1 个，我需要停止此循环。

计算值在目标值的 10% 以内。
或

x = .75

如果计算与目标不匹配，则循环将 x 降低 0.005 并重试。但我希望 0.75 成为 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

我的代码可能不是最有效的，但它当前可以运行。问题是它没有查看 x 下限的第二个条件。它只是继续运行，直到计算值在目标的 10% 以内。

我提前为代码格式道歉。 “直到”下面的 block 是带有“and”和“or”的代码。

Answer 1

真是个嘴巴的小伙子!提取局部变量，无需每次都反复取消引用相同的对象!

局部变量也使事情更容易调试。

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

不要使用 =在处理浮点数时。 Or x <= limit可能是您当前问题的解决方案。