Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to select other random cells | Excel Worksheet Functions | |||
Random select of text | Excel Worksheet Functions | |||
Select a random sample | Excel Discussion (Misc queries) | |||
Random select entries | Excel Discussion (Misc queries) | |||
Select random cell | Excel Worksheet Functions |