How to increment add +1 and keep in memory

Aerith

New Member
Joined
Mar 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:

Excel.png


Thank you
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
that's not possible without manual or VBA-interaction.
 

Aerith

New Member
Joined
Mar 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
If anybody knows how to do this with VBA, please let me know, thank you.
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
when does VBA have to increment, when you push on a button, when the sheet recalculates ?
When is the right moment ?
 

Aerith

New Member
Joined
Mar 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,339
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

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.
 

Forum statistics

Threads
1,176,138
Messages
5,901,574
Members
434,906
Latest member
Prabhu_Churi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top