PDA

View Full Version : Select Random cells from range



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?

SamT
11-22-2019, 09:39 AM
This is your code algorithm

Do if empty cell is hit 5 times
If IsEmpty(random cell) Then other random cell = 1 'regardless if other random cell is empty
Loop

What I would do is fill a 5 item Array with unique random Cell numbers, then for each item in that array set that cell value to 1.

Paul_Hossler
11-22-2019, 10:59 AM
I'm not sure that it's reliable

I usually do something like this




Option Explicit


Sub Generate()
Dim A(1 To 9, 1 To 2) As Double
Dim i As Long, j As Long
Dim A1 As Long, A2 As Double

Randomize

'fill array with index and random number
For i = LBound(A, 1) To UBound(A, 1)
A(i, 1) = i
A(i, 2) = Rnd
Next i

'sort by random number
For i = LBound(A, 1) To UBound(A, 1) - 1
For j = i To UBound(A, 1)
If A(i, 2) > A(j, 2) Then
A1 = A(i, 1)
A2 = A(i, 2)
A(i, 1) = A(j, 1)
A(i, 2) = A(j, 2)
A(j, 1) = A1
A(j, 2) = A2
End If
Next j
Next i

'use first 5 indecies to determine which cells to make = 1
With ActiveSheet.Range("A1:I1")
.ClearContents
For i = 1 To 5
.Cells(1, A(i, 1)).Value = 1
Next i
End With

End Sub