With code, how would I randomly select/pick say 8 cells in the range B5 to O5, and only if blank?

Thanks,
Howard

Hi Howard,

Am Thu, 12 Nov 2015 04:20:32 -0800 (PST) schrieb L. Howard:

With code, how would I randomly select/pick say 8 cells in the range B5 to O5, and only if blank?

the more cells that are not empty the less empty cells you can pick.Your
range only has 14 cells.
But try:

Sub RandomCells()
Dim rngBig As Range
Dim varRnd() As Variant, varNew() As Variant
Dim i As Long
Dim myDic As Object

Do
n = Application.Min(Int(Rnd * 15) + 2, 15)
If Len(Cells(5, n)) = 0 Then
ReDim Preserve varRnd(i)
varRnd(i) = n
i = i + 1
End If
Loop Until i = 14

Set myDic = CreateObject("Scripting.Dictionary")
For i = LBound(varRnd) To UBound(varRnd)
myDic(varRnd(i)) = varRnd(i)
Next
varNew = myDic.items

For i = 0 To myDic.Count - 1
If rngBig Is Nothing Then
Set rngBig = Cells(5, varNew(i))
Else
Set rngBig = Union(rngBig, Cells(5, varNew(i)))
End If
Next
rngBig.Select
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

Thanks for the code.

Real close, is there a way to refer to a cell on the sheet for the exact number of cells to randomly select.

The value would be in Q5, which I declared as 8.

On a second run of the code the value will be in R5 and will be 3.

Third run is for the value in S5 which is 3.

The values may vary from Q, R, & S but will never add to more than 14.

With each run of the code, I will be trying to write some values to those selections. And each set of selected cells will get a value from a different range of values.

And there are six rows to do these three runs on.

May need some help on that, but trying to do it myself for now.

Howard

Hi Howard,

Am Thu, 12 Nov 2015 06:22:26 -0800 (PST) schrieb L. Howard:

The value would be in Q5, which I declared as 8.

On a second run of the code the value will be in R5 and will be 3.

Third run is for the value in S5 which is 3.

The values may vary from Q, R, & S but will never add to more than 14.

With each run of the code, I will be trying to write some values to those selections. And each set of selected cells will get a value from a different range of values.

And there are six rows to do these three runs on.

are the cells at start all empty? Where is the range for these runs?
What do you mean with 6 rows for the 3 runs?
Where are the values the should be entered into the selected cells?
Can you send me a workbook with some examples?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional