Can't get a "Do Until" loop to work properly

[loderdodger]

Hi,
I have a loop that isn't working and it's beyond me what is causing the trouble. If anyone can notice what may be causing the issue I would be extremely grateful.

I have two loops, the first one works fine (which is why I want to show it), and the second one is basically the exact same operation just with different range references, and it is the second one that isn't working properly.

What is happening is that it should stop when: Range("SumMKT") <= Range("MKTBudget"). The loop does eventually stop, but it should be stopping earlier than it is. If it helps I can also explain that "SumMKT" is decreasing every time a cell is set to 0: Bcell.Offset(0, 4) = 0

I also wanted to add that the only thing that seems that different from the two loops is the:
Do Until Range("SumMKT") <= Range("MKTBudget")

However, I tried typing in the actual number so instead of Range("MKTBudget") I put in the constant (20,000,000) and it still doesn't work.

Thanks!


This is the first loop that is working fine:

Sub optimize()
Sheets("Optimization-Backend").Select

Dim Bcell As Range

Do Until Range("MKT_RD_belowBGT") = 1
For Each Bcell In Range("B2:B76")

If Bcell = Range("MinROI") Then
Bcell.Offset(0, 4) = 0
End If

Next Bcell
Loop

'Optimize1_stage2

End Sub


The second one:


Sub Optimize1_MKT()

MsgBox ("Optimize Marketing")

Dim Bcell As Range

Do Until Range("SumMKT") <= Range("MKTBudget")
For Each Bcell In Range("B52:B76")

If Bcell = Range("MinROI_MKT") Then
Bcell.Offset(0, 4) = 0
End If

Next Bcell
Loop



End Sub

 

[xenou]

Since the inner loop (the For-Each Loop) has no condition, it will always operate on all the cells (B2:B76). Is that what you want? Your description above sounds as though you'd like all operation to cease as soon as the condition is met.

From your description, it sounds like this is what you want. Food for thought, anyway:

MsgBox ("Optimize Marketing")
Dim Bcell As Range

For Each Bcell In Range("B52:B76")
    If Bcell.Value = Range("MinROI_MKT").Value Then
        Bcell.Offset(0, 4).Value = 0
    End If
    
    [COLOR="SeaGreen"]'//Early exit criterion[/COLOR]
    If Range("SumMKT").Value > Range("MKTBudget").Value Then Exit For
Next Bcell

 

[loderdodger]

Excellent! Thanks xenou! Works perfectly.

I'm wondering why the first loop was working properly and not this second one? Any ideas? Or would you recommend changing the code of my first loop to how you have set up the second one?

Thanks again

 

[xenou]

Could it be coincidence? Maybe the threshold was never reached even though you were looping all the cells in the FOR loop!

At any rate, it seems to me the first sub should be edited in the same manner.

ξ