Increasing count when random value changes-CodePudding

I want to add 1 to the count every time a value between 1 and 10 occurs.

So for example, when the random number is a 2, I want to add 1 to the count for 2. When the next random number is generated, let's say it's a 5, the count for 2 remains as it is and 1 is added to the count for 5 and so on.

The random number is generated in a cell on Sheet 1.

The table displayed below is on Sheet 2. I am using an IF statement in the Result Yes? column to return a 1 when the random number corresponds.

I understand this may require a VBA solution.

Any help would be great!

Count table

CodePudding user response：

Heres my best guess at what you are trying to acheive.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("yourWorksheetsName")
    
    Randomize
    Dim rndVal As Integer: rndVal = Int((11 * Rnd))
    
    ws.Cells(2, 6).Value = rndVal
    
    ws.Range(ws.Cells(2, 2), ws.Cells(12, 2)).ClearContents
    ws.Cells(rndVal   2, 2).Value = "Yes"

    ws.Cells(rndVal   2, 3).Value = ws.Cells(rndVal   2, 3).Value   1

End Sub

With this implementation you will need to add the code to the worksheet which will trigger the random value to update. (I read your comment and saw what you actually wanted to do).
Good luck