Exiting a VBA loop with multiple variables

Question

As a newbie in VBA (Excel), I am trying to make a tool, which determines what the diagnostic yield of a certain test must be; in order to be cost-effective as a pre-screening to another diagnostic test.

What I want it to do is calculate for a certain yield of test A, at what yield for test B the costs per diagnosis are the same for both tests. The code I wrote has to loop for a certain range for the diagnostic yield and exit this loop when the costs per diagnosis for test A drop under the costs per diagnosis for test B.

However, the code keeps looping for this range, but does not stop when my condition on costs is met. I tried a lot, including do while and do until statements, but it just won't work. I really hope someone could help me out! Many thanks in advance! Kirsten

Sub TGP_WES_OR_WES()
    Dim Yield_A As Double
    Dim Yield_B As Double
    Dim Yield_A_max As Double

    Dim Cost_diagnosis_A As Double
    Dim Cost_diagnosis_B As Double

    Yield_B = Range("C6")
    Yield_A_Max = Yield_B - 0.1

    Cost_diagnosis_B = Range("E15")
    Cost_diagnosis_A = Range("E11")

    Do While Yield_A < Yield_A_max
        For Yield_A = 1 To Yield_A_max Step 0.1
            Range("C5").Value = Yield_A
            If Cost_diagnosis_A < Cost_diagnosis_B Then
                Exit For
            End If
        Next Yield_TGP
    Loop

    Range("D1").Value = Yield_TGP
End Sub

Answer 1

You have a double loop (both of which appear to be doing the same thing):

Do While Yield_A < Yield_A_max
    For Yield_A = 1 To Yield_A_max Step 0.1
        ...
    Next Yield_TGP
Loop

Remove the outer do loop for starters.

The 2nd issue I see is that your loop exit conditions do not appear to depend on the loop iteration. That is to say Cost_diagnosis_A and Cost_diagnosis_B are not updated or changed by the loop. This generally indicates a design error as nearly all loop termination conditions will be dependent upon a value the loop is calculating or updating (or overall loop progress). Intuitively, your loop termination condition should incorporate Yield_A either directly or indirectly (from a downstream calculation). Perhaps you want to be updating the values of Cost_diagnosis_A and/or Cost_diagnosis_B inside your loop body, based on Yield_A?