Count Occurences of a Cell If It's Less Than

[matthewlouis]

In my probability analysis spreadsheet, I have a single cell (E10) that contains a %. My goal is to create a counter where, on a mouse click, it will count the number of times cell E10 <=80%.

If I do 30 clicks, 40 clicks, just a simple set of iterations . . . I would like to see the cell count the times those clicks were less <=80%.

What would the formula look like for that?

Thanks in advance!

 

[JamesCanale]

Try this:

Sub clicky()
for t = 1 to 100
Application.Calculate
Range("G10").Value = Range("G10").Value + 1
If Range("E10").Value <= 0.8 Then Range("G11").Value = Range("G11").Value + 1
debug.print Range("E10").Value
next t
End Sub

and see what ends up in the debug window at the bottom of the VBA window - make sure to show it if it's not there. That will give you a picture of the output - and you might see where it is going wrong.

Maybe you are using 80 for the percentage, and I'm using .8.

 

[matthewlouis]

OK, I modified the algo. I changed percentage to a number and you see 0.45 -- but it wasn't counted.(?)

I am not seeing any of these numbers change when I run the macro -- only the number to 100 runs.

 

[JamesCanale]

Did yo look in the immediate window?

 

[matthewlouis]

Yes, when I put in the Debug.Print line, it didn't show anything in the immediate window except the Range("G10").Value = Range("G10").Value + 1 was YELLOW (see attachment)

There wasn't any verbiage like you're showing in your above post..

That cell (G10) is a link . . . should we have a direct refernce to the worksheet where that calc comes from? Could that be it?

The name of the worksheet for that value is Model but the cell reference is the same.

 

[JamesCanale]

When there is a break in the code (FN+PauseBreak) VBA will highlight the current line of execution. You can hover and see the values - like what t is.

Change the line to

debug.print t&range("E10").value

 

[matthewlouis]

When there is a break in the code (FN+PauseBreak) VBA will highlight the current line of execution. You can hover and see the values - like what t is.

Change the line to

debug.print t&range("E10").value

I changed the line, hit Ctrl-S and got this . . . don't worry about the cell changes as what was E10 is now F13

 

[JamesCanale]

THe same odd thing with the semicolon happened to me - put this in:

Debug.Print t & ":" & Range("E10")

 

[matthewlouis]

THe same odd thing with the semicolon happened to me - put this in:

Debug.Print t & ":" & Range("E10")

OK, I updated that line, Ctrl-S, it ran, and then Alt-Q, and I got this.

 

[matthewlouis]

When I try to run macro, it pops up with this.

 

[matthewlouis]

Again, I am noticing the macro is running the 100 iterations . . . . but the numbers are not changing in the boxes, i.e., I don't see 82% or 90% or 96% of 100%, etc... it's like no calculations are occuring to count (?) or is it, I don't know. I do notice as it counts to 100, the bottom of the worksheet is calculating (it's blinking) like something is calculating..