Random numbers but they should only appear x times in a row-CodePudding

I want to add 0 or 1 as numbers (randomly) in a lot of cells (in different rows and columns). For example:

0  1  1  1 0  0 0 0
1  1  0  0 0  1 1 0

But I want those numbers be added up to five times in a row. For example: if the row has 10 cells which the random numbers will go, I don't want the 1 or 0 to be added a sixth time so I am looking to something like this: 0 1 1 1 0 1 1 0 0 0

CodePudding user response：

Based on your latest comment, the following code would work:

Sub test()
    create_binary Range("A1:J1"), 5
End Sub

Sub create_binary(rng As Range, one_count As Long)
    With rng
        .ClearContents
        Do Until Application.WorksheetFunction.Sum(.Cells) = one_count
            .Cells(Int(.Count * Rnd   1)) = 1
        Loop
        .SpecialCells(xlCellTypeBlanks).Value = 0
    End With
End Sub

It contains a subroutine called create_binary which needs two parameters:

The cells to write to (rng)
The total value you want those cells to reach (one_count)

So if you want a row of 10 cells from A1 to J1, with 8 of them reading 1 and the remaining 2 reading 0, you'd use:

create_binary Range("A1:J1"), 8

But if you wanted a column of 8 cells from A1 to A8, with 5 of them reading 1 and the remaining 3 reading 0, you'd use:

create_binary Range("A1:A8"), 5