Looking for VBA or Macro Counting a specific value occuring in single, random, changing cell?

blackjack · #1 09-02-2014, 08:33 PM

How do you count how often a specific value occurs in single changing cell? I thought =countif would work but everytime the cell changes it takes the count back to 0. I'm using random numbers and would like to count each time the number is below .1. I need a running total every time the random number in A1<.1. I need it to add 1 each time the random number is less than .1. The reset to 0 is what I'm trying to avoid. I'm looking for a way to have A2 add to itself each time the condition is met.

example (Random numbers generate in cell A1)
A1=.06, A2=1
recalculate A1=.4, A2=1
recalculate A1=.09, A2=2

Posted here first but was recommended to come here for VBA help.

https://www.msofficeforums.com/excel...html#post69714

macropod · #2 09-11-2014, 11:27 PM

You could use a macro like the following in the worksheet's code module:

Code:

Private Sub Worksheet_Calculate()
Application.Calculation = xlCalculationManual
If Range("A10").Value < 0.1 Then
  Range("b10").Value = Range("b10").Value + 1
End If
End Sub

Note that the code sets the Calculation mode to Manual. That's necessary to stop the update forcing another re-calc. It also means you'll need to manually re-set the calculation mode when you're done.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do you count how often a specific value occurs in single changing cell?	blackjack	Excel	12	09-03-2014 10:41 AM
Sum values within a single cell	shabbaranks	Excel	17	05-13-2014 06:47 PM
Word counting macro for newly added portion?	New Daddy	Word VBA	24	09-30-2013 07:30 PM
How to fill a cell with random numbers to do calculations?	zanat0s	Excel	1	06-13-2011 10:33 AM
How can I write the following (whole) formula in a single cell?	Learner7	Excel	1	07-19-2010 10:06 AM