Another question on selecting cells from a range of cells

[Mike]

I have a question similar to a previous question I asked
on how to select cells from a range of cells. Here's my
new problem. I have 40000 numbers in a column and this
time I need to select 25 random numbers from a
subgrouping of 200 numbers in that column. I need 25
random numbers from every 200 numbers up to 40000. So
basically from cells 1-200 I need 25 random cells from
that group copied to a different column and then from the
201-400, and then from 601-800, and so on. I tried to
alter some previous suggestions on a similar problem to
do random cells but I haven't been able to get it to run
without errors. Any help is greatly appreciated.

 

[JE McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/randint.html

Say your 40000 numbers are in column A.

Select B1:B25 and array-enter:

=INDEX($A:$A,RandInt(INDEX((COLUMN()-2)*200+1,1),
INDEX((COLUMN()-1)*200,1)))

Copy to the right.

 

[Tom Ogilvy]

Sub test()
Dim rng As Range, rng1 As Range
Dim a As Long, b As Long
b = 1
For a = 1 To 40000 Step 200
Set rng = Cells(a, "A").Resize(200, 1)
rng.Copy Destination:=Cells(b, 5)
Set rng1 = Cells(b, 5).Resize(200, 1)
rng1.Offset(0, 1).Formula = "=rand()"
rng1.Resize(200, 2).Sort Key1:=rng1.Offset(0, 1), _
Order1:=xlAscending, Header:=xlNo
rng1.Offset(0, 1).ClearContents
rng1.Offset(25, 0).ClearContents
b = b + 25
Next
End Sub