jackdandcoke
09-30-2008, 10:46 AM
I'm running a contest online that dumps entries into an excel sheet. I want to run a macro that randomly picks five winners from the list.
Originally I was going to generate a random number in column d using an if statement. =IF(ISNUMBER(C1),RANDBETWEEN(1,100),null).
What I would really like it to do is generate a random number in the d column if there is a value in the c column, then I would have my macro sort the numbers ascending and select the top 5 rows.
I'm not good with ranges in vba or excel and this is a static range so I'd really like the code to say
=if(isnumber(c1 'this of course has to apply to any cell in c' , RANDBETWEEN(1, 'here I would like it to count how many rows there are in c column' ),null)
My only problem really is how to get it to know what the range should be for the RANDBETWEEN and how to get it to not return anything instead of #NAME? if there is nothing in the c cell.
That is unless someone knows how to code it to pick 5 random lines and copy the contents of cells A,B, and C from those lines and paste them into another work sheet.
Originally I was going to generate a random number in column d using an if statement. =IF(ISNUMBER(C1),RANDBETWEEN(1,100),null).
What I would really like it to do is generate a random number in the d column if there is a value in the c column, then I would have my macro sort the numbers ascending and select the top 5 rows.
I'm not good with ranges in vba or excel and this is a static range so I'd really like the code to say
=if(isnumber(c1 'this of course has to apply to any cell in c' , RANDBETWEEN(1, 'here I would like it to count how many rows there are in c column' ),null)
My only problem really is how to get it to know what the range should be for the RANDBETWEEN and how to get it to not return anything instead of #NAME? if there is nothing in the c cell.
That is unless someone knows how to code it to pick 5 random lines and copy the contents of cells A,B, and C from those lines and paste them into another work sheet.