How to increment add +1 and keep in memory

[Aerith]

Hello,

I want to count the number of times a value (that has Randbetween function) displays the number 100.
But when that value displays another number than 100, I want the counter to keep in memory how many times 100 had appeared.

I provided a basic picture to explain it better:

Thank you

 

[BSALV]

that's not possible without manual or VBA-interaction.

 

[Aerith]

If anybody knows how to do this with VBA, please let me know, thank you.

 

[BSALV]

when does VBA have to increment, when you push on a button, when the sheet recalculates ?
When is the right moment ?

 

[Aerith]

I created a button that refreshes the worksheet.
Optimally, the VBA would increment upon use of the button.
The worksheet has a bunch of RandBetween, one of them would be the cell targetted by the VBA code.

 

[Aerith]

Someone helped me so if anyone is looking for the same thing, here is how he told me:

Put this in a module:

Function CountNum100(r As Range)
Static count100 As Long
If r.Value = 100 Then count100 = count100 + 1
CountNum100 = count100
End Function

And then this in a cell, B3 in this example:

=CountNum100(B1)

Thank you

 

[BSALV]

Private Sub CommandButton1_Click()
     MsgBox "other stuff"

     With Range("A1")
          .Value = .Value - (Range("B1").Value = 100)           'increment A1 if B1 is 100
     End With

     With Range("A10")
          .Value = .Value + WorksheetFunction.CountIf(Range("D1:G100"), 100)
     End With




     MsgBox "other stuff"
End Sub

 

[RoryA]

As you should know by now ():

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

How to increment add +1 and keep in memory

Hello, I want to count the number of times a value (that has Randbetween function) displays the number 100. But when that cell displays another number than 100, I want the counter to keep in memory how many times 100 had appeared. I provided a basic picture to explain it better. 763065...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.