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