Select a random cell < 1
 

I'm not a programmer so I'm struggling with this.

I have a dynamic range of cells (say A1:A600, but will
expand beyond A600). These cells get individually
populated over time with a 1. I'd like a routine to
identify all cells in the range *without* a 1, and then
from this collection, choose a random cell (or row #,
since it's all in col. A).

Any sample code would be appreciated.
Thanks.
Jason

Select a random cell < 1
 

Hi Jason
would a non VBA solution also work for you?

try the following array formula (entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A600,SMALL(IF(A1:A600<1,ROW(A1:A600)),R ANDBETWEEN(1,COUNTIF(
A1:A600,"<1"))))


Note: the Analysis Toolpak Addin has to be installed (for RANDBETWEEN)


--
Regards
Frank Kabel
Frankfurt, Germany


Jason Morin wrote:
I'm not a programmer so I'm struggling with this.

I have a dynamic range of cells (say A1:A600, but will
expand beyond A600). These cells get individually
populated over time with a 1. I'd like a routine to
identify all cells in the range *without* a 1, and then
from this collection, choose a random cell (or row #,
since it's all in col. A).

Any sample code would be appreciated.
Thanks.
Jason

Select a random cell < 1
 

Sub aaatest()
Dim rng As Range, rng1 As Range
Dim num As Long, dim i as long
'For i = 1 To 100
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
num = Int(Rnd() * rng.Count + 1)
Cells(num, 1).Interior.ColorIndex = 5
Do While True
If IsNumeric(Cells(num, 1)) Then
If Cells(num, 1) < 1 Then
Set rng1 = Cells(num, 1)
Exit Do
End If
Else
Set rng1 = Cells(num, 1)
Exit Do
End If
num = num + 1
If num rng.Count Then _
num = 1
Loop
If Not rng1 Is Nothing Then
' rng1.Offset(0, 1).Value = rng1.Offset(0, 1).Value + 1
msgbox rng1.Address
Else
MsgBox "all filled with 1"
End If
'Next
End Sub

This approach is not uniformly random. Cells located after a cell with a 1
in it will have a higher probability of being picked. Don't know if this is
an issue or not. If it is, it could be modified to continue to draw random
numbers until it hits a cell without a 1.

--
Regards,
Tom Ogilvy




"Jason Morin" wrote in message
...
I'm not a programmer so I'm struggling with this.

I have a dynamic range of cells (say A1:A600, but will
expand beyond A600). These cells get individually
populated over time with a 1. I'd like a routine to
identify all cells in the range *without* a 1, and then
from this collection, choose a random cell (or row #,
since it's all in col. A).

Any sample code would be appreciated.
Thanks.
Jason

Select a random cell < 1
 

The original didn't properly check for the condition where all cells
contained 1. Here is a correction. Also, I left in a command to color
cells which I used to test the functionality in an outer loop. That wasn't
needed.

Sub aaatest()
Dim rng As Range, rng1 As Range
Dim num As Long, i As Long
Dim firstNum As Long
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
num = Int(Rnd() * rng.Count + 1)
firstNum = num
Do While True
If IsNumeric(Cells(num, 1)) Then
If Cells(num, 1) < 1 Then
Set rng1 = Cells(num, 1)
Exit Do
End If
Else
Set rng1 = Cells(num, 1)
Exit Do
End If
num = num + 1
If num = firstNum Then Exit Do
If num rng.Count Then _
num = 1
Loop
If Not rng1 Is Nothing Then
' rng1.Offset(0, 1).Value = rng1.Offset(0, 1).Value + 1
MsgBox rng1.Address
Else
MsgBox "all filled with 1"
End If
End Sub



--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Sub aaatest()
Dim rng As Range, rng1 As Range
Dim num As Long, dim i as long
'For i = 1 To 100
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
num = Int(Rnd() * rng.Count + 1)
Cells(num, 1).Interior.ColorIndex = 5
Do While True
If IsNumeric(Cells(num, 1)) Then
If Cells(num, 1) < 1 Then
Set rng1 = Cells(num, 1)
Exit Do
End If
Else
Set rng1 = Cells(num, 1)
Exit Do
End If
num = num + 1
If num rng.Count Then _
num = 1
Loop
If Not rng1 Is Nothing Then
' rng1.Offset(0, 1).Value = rng1.Offset(0, 1).Value + 1
msgbox rng1.Address
Else
MsgBox "all filled with 1"
End If
'Next
End Sub

This approach is not uniformly random. Cells located after a cell with a
1
in it will have a higher probability of being picked. Don't know if this
is
an issue or not. If it is, it could be modified to continue to draw
random
numbers until it hits a cell without a 1.

--
Regards,
Tom Ogilvy




"Jason Morin" wrote in message
...
I'm not a programmer so I'm struggling with this.

I have a dynamic range of cells (say A1:A600, but will
expand beyond A600). These cells get individually
populated over time with a 1. I'd like a routine to
identify all cells in the range *without* a 1, and then
from this collection, choose a random cell (or row #,
since it's all in col. A).

Any sample code would be appreciated.
Thanks.
Jason