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!

 

[Fluff]

Whilst in the Editor press Ctrl G and the immediate window should open up.

 

[matthewlouis]

Thanks, Fluff!!

OK, James, as you can see, there is nothing in the immediate window.

 

[matthewlouis]

Thanks, Fluff!!

OK, James, as you can see, there is nothing in the immediate window.

James, this is what I meant to send.

 

[JamesCanale]

That debug.print statement in the code is putting a count to 100 in the immediate window, followed by the contents of the cell. It looks like the macro just isn't running. It could be a macro security issue. Check under the developers tab, Macro Security. Sometimes it is necessary to restart excel.

 

[matthewlouis]

That debug.print statement in the code is putting a count to 100 in the immediate window, followed by the contents of the cell. It looks like the macro just isn't running. It could be a macro security issue. Check under the developers tab, Macro Security. Sometimes it is necessary to restart excel.

OK< do I have the right selection here? I don;t use macros (as you can tell) so maybe I don't have the right box checked (?) When I open the sheet, it does not ask me if I want to run a macro, it just opens.

 

[matthewlouis]

That debug.print statement in the code is putting a count to 100 in the immediate window, followed by the contents of the cell. It looks like the macro just isn't running. It could be a macro security issue. Check under the developers tab, Macro Security. Sometimes it is necessary to restart excel.

I changed the settings as you can see to enable, closed the worksheet, reopened, and it still isn't running. In my ribbon at the top, under the Develepor tab, the macro button has a yield sign next it (?)

 

[JamesCanale]

I tend to have mine on the second one (disable with notification) and then I enable the macros in a pop up window when I load the file. You might close excel and reopen, not just the sheet.

 

[matthewlouis]

I tend to have mine on the second one (disable with notification) and then I enable the macros in a pop up window when I load the file. You might close excel and reopen, not just the sheet.

OK, did that, close Excel, reopened, and algo still isn't running. I am not getting any message that asks me if I want to enable a macro (?)

The algo just isn't calculating, only counting. What's weird is I see calculating at the bottom of the sheet, very fast, but none of my numbers are changing, thus no occurrences.

 

[JamesCanale]

And the immediate window in VBA doesn't have any 1-100 in it?... Did you save the file as an .xlsm file? you can put a msgbox function in there, that will give you a pop up if it's working.

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 t & ":" & Range("E10")
MsgBox t
Next t
End Sub

 

[matthewlouis]

I will update the algo with what you just posted . . . I have saved it as a macro-enabled workbook. Same thing as .xlms ?