Macro to count if cell result equals 1 or 2 from random generation

[moe10134]

Hello,

Any of you macro genius' out there know of any VB Code I can use to create an ongoing tally of results in the same cell? If cell B2 = 1 or 2 [will not be anything else except 1 and 2], Does anyone know how to create a tally for counting how many times a 1 appears and how many 2's appear in cell C2 & D2?

There are a lot of ways this macro can perform. One example would be adding a "1" in one cell row, then next time adding a "1" in next cell row, etc. to create a =COUNTA of all "1"'s . Then in next columm, same thing for "2", =COUNTA of all "2"'s.

1
1
1
1

2
2
2
2

Or placing the exact number that appears in B2 in a separate column as well, For an =COUNTIF.

1
1
2
1
2
2


Or having two cells, one cell to record for each "1", one cell to record for each "2" and a macro will count how many times, the 1 appears and how many times the 2 appears.


I am looking for a macro because it is a random generation of 1 or 2. Each time I click on the enter key after data in any cell in workbook or hit F9, the rand number changes to 1 or 2. I could track this manually with the examples above, but I cannot always keep any eye on that cell and note each time it changes. I'm looking for a way for Excel to automate it.

Any macro guru's got any suggestions? All your help is appreciated.

Kind Regards

 

[My Aswer Is This]

Try this:

Assuming the value in range "B2" is changing do to a manual change and not the result of a formula.
Try this:

If the value in "B2" changes to a "1" the results will add up in "C2" and the same will happen in "D2" if the value entered in "B2" changes to a "2"


This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 2 Then
If Target.Value = 1 Then Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + 1
If Target.Value = 2 Then Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + 1
End If
End Sub

 

[moe10134]

Hello,

Thank you for getting back to me on this. Looking at this code it looks like this will work. I want to mention that it is a =RANDBETWEEN(1,2) function that is generating the 1 & 2. When I copy and paste the code into my worksheet, nothing appears in cell C2 or D2 when I open it each time. Is this due to the results being a formula instead of a static 1 or 2? I can always create a macro to Run the =RANDBETWEEN(1,2) and the copy and Paste Values in the cell B2 to make it a static number. I would still like it to generate this code in cells C2 and D2. Could you send me this same code to assign this code to a macro button or shortcut key? When I push the Run button with this code now, nothing happens. I also cannot Step Into the macro either. Nothing happens. I think the macro button or shortcut key would work better. That way I can tell if things are happening correctly. My file is saved as an .xlsm Macro-Enabled File. Also, on a side note, I cannot change my macros to Workbook from (General) in the VB Editor. Any suggestions on that? You have been a great help for me on this. I greatly appreciate it!

 

[My Aswer Is This]

Well you said:
"I want to mention that it is a =RANDBETWEEN(1,2) function"

If its a random number between 1 and 2 then the number would be something like:
1.1 or 1.5 or 1.6 etc.

But your subject title says:
Macro to count if cell result equals 1 or 2 from random generation.

So I'm confused.

1 and 2 are not between 1 and 2