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

loderdodger

New Member
Joined
Jun 9, 2008
Messages
7
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
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:

Code:
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
 
Upvote 0
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
 
Upvote 0
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.

ξ
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top