[VBA] Do Until range ("W1").value >= TGT (0.2)

[RockandGrohl]

Hi all,

Have a value in W1 that started at 0.13 (or 13%)

I'm trying to execute a loop up until the point where the value in W1 exceeds the TGT, which is set as "0.2"

At the bottom of the loop, the value in W1 updates to be higher or lower.

Currently, starting at 0.13 and aiming to be equal or higher than 0.2, my loop is at 0.34 and still going.

Do I need to declare this as a variable, a variant or what?

EUAllocation.EUTarget.Caption = Format(EUValue / 100, "0%")

~~~~~~~~ NEW SUB ~~~~~~~~~~

Private Sub EUStart_Click()


Set ads = Worksheets("Adselect")
Set atm = Worksheets("ATM")


Application.ScreenUpdating = False


TGT = Format(EUAllocation.EUTarget.Caption, "0.0")


[Lots of irrelevant code]

Do Until Range("W1").Value >= TGT
Range("A3").Activate
Range("W1").FormulaR1C1 = "=COUNTIF(C7,""*EU*"")/COUNTIFS(C6,""Just Go"",C11,""Y"")"
Range("W1").Value = Range("W1").Value
Loop

Thanks!

 

[steve the fish]

Must be missing something. How can that formula ever produce a different number in that loop? Will just produce the same result over and over.

 

[RockandGrohl]

Must be missing something. How can that formula ever produce a different number in that loop? Will just produce the same result over and over.

That's the "Irrelevant code"

The formula counts the number of EU templates as a percentage of total templates, the code checks to see if the number of EU tours is less than the target (in this case, 13.1% with a target of 20%) and then executes the code, which finds a UK template and changes it to EU, then re-counts the percentage.

When I step through the code it works as expected. It will go 13.1, 13.8, 14.5, 15.2, 15.9 etc etc, until it gets to 19.7, then it will do 20.4 and the code should stop (those figures are all percentages btw) but it just carries on.

I've changed to this:

TGT = Format(EUAllocation.EUTarget.Caption, standard)

Which when I hover over it, it shows "0.20" as TGT

But it still happens.

The value in W1 is a numeric, currently "0.2154462532"

 

[jmacleary]

Hi there. I think Steve is onto the problem - if you move your "lots of irrelevant code" to immediately after the Do Until line it should work. Also, is tgt defined as a number? If not, comparing it to a value will give strange results.

 

[RockandGrohl]

Solved, although unideally, I write the value of the target to cell V1 and then set that as TGT.

So in V1 is "0.2" and that's what is set.

Hoping there's a way to do it without writing to a helper cell, thanks.

 

[RockandGrohl]

Hi there. I think Steve is onto the problem - if you move your "lots of irrelevant code" to immediately after the Do Until line it should work. Also, is tgt defined as a number? If not, comparing it to a value will give strange results.

Hello, did you read my reply to Steve?


The test is at the top:

"Is the current percentage of EU templates equal to or above the target?"

If not, then

[code that changes one UK template to EU]

[code that recalculates the percentage]
[loop]

Once it loops, it checks the recalculated percentage against the same target, if it's still below it changes another UK template to an EU one.


I know it should work, because once I wrote the target to a cell (V1) it stops once the calculated percentage in W1 exceeds the target. The problem is I'm not declaring TGT properly and I don't know whether it should be a long, a variable, a variant or an integer or whatever. Because I'm seeing it as "0.2" but Excel is obviously reading it differently as when the calculation exceeds 0.2 it doesn't stop.

 

[steve the fish]

Ok so if you just think its a declaration problem then make it a double. But what you wrote in the opening post will never change the result of that formula as any change is not within the loop.

 

[RockandGrohl]

Ok so if you just think its a declaration problem then make it a double. But what you wrote in the opening post will never change the result of that formula as any change is not within the loop.

Steve,

I think you are really misunderstanding here.


I'll try and explain it.

We have a loop that is checking the current value against the target. Let's say the current value is 18% and the target is 20%.

Because the current value is under the target, the code within the loop executes.

The code looks for a UK template and changes it to an EU template.

Then the current value is updated, now it's 19%

Then the code loops and it goes back to the start. 19% is still less than 20%, so it finds another UK template and changes to EU. The value is updated and it's now 20.5%

We arrive back at the start of the "Do Until" loop where it again checks, this time 20.5% is now equal or greater than 20% target, and the code moves on past the loop.

When I write the target of 0.2 to a cell V1 and declare TGT as V1, the loop works perfectly. However, if I declare TGT as the value of a caption, it still says "0.2" but it fails to work within the loop.​

 

[jmacleary]

Hi Rock.

Hello, did you read my reply to Steve?

Yes, I did and I also looked at your loop.

Do Until Range("W1").Value >= TGT
Range("A3").Activate
Range("W1").FormulaR1C1 = "=COUNTIF(C7,""*EU*"")/COUNTIFS(C6,""Just Go"",C11,""Y"")"
Range("W1").Value = Range("W1").Value
Loop

As Steve says, this will loop infinitely, as you are not changing anything. Writing/rewriting the formula will always give the same result. If you can't see that, I'm sorry but that's where the problem is.

 

[RockandGrohl]

EGG ON MY FACE.

Sorry guys, I wrote the code line in THE WRONG FREAKING PLACE when I was giving the example!!!!


I meant to write

[COLOR=#333333]TGT = Format(EUAllocation.EUTarget.Caption, "0.0")[/COLOR]


Do Until Range("W1").Value >= TGT

[Lots of irrelevant code]


Range("A3").Activate
Range("W1").FormulaR1C1 = "=COUNTIF(C7,""*EU*"")/COUNTIFS(C6,""Just Go"",C11,""Y"")"
Range("W1").Value = Range("W1").Value 

Loop

But I put it in the wrong place.

So the order is

1). Do until current value reaches target
2). Swap a UK template for an EU one
3). Recalculate current value
4). Loop


Haha, sorry guys, I was getting eggy because I didn't know why what I wrote was so difficult to understand, but I totally slipped up and gave a false example, no wonder it was so confusing!