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
|