[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]

You need to use

Dim TGT As Double

or it wont work properly. Take this example:

Dim TGT As String
Dim x As Double

TGT = "10"
x = 1

Do Until Range("W1").Value >= TGT
    Range("W1") = x
    x = x + 1
Loop

When you run that check out the value of W1. Then run this:

Dim TGT As Double
Dim x As Double

TGT = "10"
x = 1

Do Until Range("W1").Value >= TGT
    Range("W1") = x
    x = x + 1
Loop

 

[jmacleary]

OK Rock, we all make mistakes (and I make more than most). It boils down to what Steve says. Declare tgt as double, or Long and that should fix it.

 

[RockandGrohl]

You need to use

Dim TGT As Double

or it wont work properly. Take this example:

Dim TGT As String
Dim x As Double

TGT = "10"
x = 1

Do Until Range("W1").Value >= TGT
    Range("W1") = x
    x = x + 1
Loop

When you run that check out the value of W1. Then run this:

Dim TGT As Double
Dim x As Double

TGT = "10"
x = 1

Do Until Range("W1").Value >= TGT
    Range("W1") = x
    x = x + 1
Loop

Thanks, that worked perfectly and now my code looks a little cleaner )) Sorry about the mess earlier, that'll teach for me quickly trying to write up my issue!

 

[RockandGrohl]

OK Rock, we all make mistakes (and I make more than most). It boils down to what Steve says. Declare tgt as double, or Long and that should fix it.

Yeah thanks for bearing with me, I totally rushed the explanation.

It's really fun now that it's working so cleanly, I'm just swapping templates around with a nice userform, beats doing it by hand, wheeee!!