none
Problem with Ending a nested For Loop RRS feed

  • Question

  • So here's my problem.  I'm blending 3 products to meet certain specifications and wrote a vba code to help me get the correct blend.  I'm doing a 2 blend solution and a 3 blend solution.  I'm using a for loop to loop through ratios between the two products and using an exit for statement to stop the loop when the ratios blend will meet the specs I input.  The two blend code works fine and is show below.  All of the stuff in the if statement are variables but to keep the length of this down I didn't include that code.

    Dim ratio1
    For ratio1 = 100 To 0 Step -1
    Range("I11").Value = ratio1
    Range("I12").Select
    ActiveCell.FormulaR1C1 = 100 - ratio1
    If Fsulf < SulfSpec And FAsh < AshSpec And FH2O < H2OSpec And FFlash > FlashSpec Then
    Exit For
    End If
    Next ratio1

    My issue is in trying to make it a 3 product blend.  What I'm doing is starting with 100% of the first product and 0% combined of the second two.  I have a for loop to run through the second two blends as a blend within themselves starting with one at 100% and one at 0 and then looping through.  If the blend works I need it to stop.  The code is below and its not stopping when it should.  Any suggestions?

    For ratio1 = 100 To 0 Step -1
    Range("I11").Value = ratio1
    Ratio2 = 100 - ratio1    
            For Ratio3 = 100 To 0 Step -1
            Range("I12").Value = Ratio3 * (Ratio2 / 100)
            Range("I13").Value = (100 - Ratio3) * (Ratio2 / 100)
            If Fsulf < SulfSpec And FAsh < AshSpec And FH2O < H2OSpec And FFlash > FlashSpec Then
            Exit For
            End If
            Next Ratio3   
        If Fsulf < SulfSpec And FAsh < AshSpec And FH2O < H2OSpec And FFlash > FlashSpec Then
        Exit For
        End If
    Next ratio1

    Monday, August 25, 2014 10:02 PM

All replies

  • Re: letting go

    Using Exit For just delivers you to the next loop.
    It get out use Exit Sub or Exit Function.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:36 PM
    Tuesday, August 26, 2014 4:21 AM
  • I tried substituting exit sub and it still doesn't work.  It works with the two blend but not the 3.  Even if I make all of the blends meet spec so the loop should stop after the first run it still runs through everything.  There's got to be something simple I'm missing here either in how I have the code ordered or something else.  I tried the Exit Function too and it got mad at me (said that Exit Function was not allowed in sub).
    Tuesday, August 26, 2014 4:53 PM
  • Re: Letting go

    You can determine the where/what/why of the "non exit" by putting in a breakpoint at each exit line.
    When/If the code stops then you can tap the F8 key to advance line by line to determine how VBA is evaluating your code.
    A break point can be set by just clicking the left margin at a particular line or using the Breakpoint item on the Debug menu.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)


    • Edited by James Cone Sunday, October 30, 2016 11:37 PM
    Tuesday, August 26, 2014 6:11 PM