ste_wilko84
11-22-2019, 08:05 AM
Hi guys,
What I am attempting to do is have a range of 9 cells from a row and select 5 at random and fill them with the number 1 with this code
Private Sub Generate()
Randomize
Dim i As Integer
Dim rand As Integer
rand = 0
With ThisWorkbook.Worksheets("Sheet2").Range("A1:I1")
Do While rand < 6
If IsEmpty(Cells(Application.WorksheetFunction.RandBetween(1, 9))) Then
.Cells(Application.WorksheetFunction.RandBetween(1, 9)).Value = 1
rand = rand + 1
End If
Loop
End With
End Sub
It throws no errors, but what it doesn't always do is return 5 cells with the number 1 in. Sometimes it does 5, others it will do 4 or 3.
I need specifically 5 out of the 9 cells to hold a value, and the other 4 to be blank.
I've just re-looked at my code and I think I know where it is going wrong, I am checking for a blank cell, if it finds one it then selects another cell; but I don't know what to put in the if statement when it finds a blank cell
Can anyone help?
What I am attempting to do is have a range of 9 cells from a row and select 5 at random and fill them with the number 1 with this code
Private Sub Generate()
Randomize
Dim i As Integer
Dim rand As Integer
rand = 0
With ThisWorkbook.Worksheets("Sheet2").Range("A1:I1")
Do While rand < 6
If IsEmpty(Cells(Application.WorksheetFunction.RandBetween(1, 9))) Then
.Cells(Application.WorksheetFunction.RandBetween(1, 9)).Value = 1
rand = rand + 1
End If
Loop
End With
End Sub
It throws no errors, but what it doesn't always do is return 5 cells with the number 1 in. Sometimes it does 5, others it will do 4 or 3.
I need specifically 5 out of the 9 cells to hold a value, and the other 4 to be blank.
I've just re-looked at my code and I think I know where it is going wrong, I am checking for a blank cell, if it finds one it then selects another cell; but I don't know what to put in the if statement when it finds a blank cell
Can anyone help?