Fill with random numbers

[Dave]

Hi,

The range A1 - H200 is 1600 cells and I need to fill those cells randomely
with the numbers 1 to 1600, no number must repeat. I played with simply
putting those numbers in the cells and used RAND() in a helper column but
this doesn't really give the randomness I require. Any help please.

D

 

[Gary''s Student]

Hi Dave: Try this. It uses columns I & J as "helper" columns:

Sub luxation()
For i = 1 To 1600
Cells(i, "I").Value = i
Cells(i, "J") = Evaluate("=rand()")
Next
Columns("I:J").Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlNo
k = 1
For i = 1 To 200
For j = 1 To 8
Cells(i, j).Value = Cells(k, "I").Value
k = k + 1
Next
Next
Columns("I:J").Clear
End Sub

 

[Dave Peterson]

You may want to visit J.E. McGimpsey's site and grab a copy of his =RandInt()
function.
http://www.mcgimpsey.com/excel/udfs/randint.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Chip Pearson]

Get the code for UniqueRandomLongs from
www.cpearson.com/Excel/RandomNumbers.aspx. This will create a
1-dimensional array with no-repeating random numbers. Then, use that
code in

Sub AAA()
Dim Arr As Variant
Dim Arr2(1 To 200, 1 To 8) As Long
Dim R As Long
Dim C As Long
Dim N As Long
N = 0
Arr = UniqueRandomLongs(1, 1600, 1600)
For R = 1 To 200
For C = 1 To 8
N = N + 1
Arr2(R, C) = Arr(N)
Next C
Next R
Range("A1:H200").Value = Arr2
End Sub

This code converts the 1D array from UniqueRandomLongs to a 2D array
and then puts that array into A1:H200.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Mike H]

Hi,

I assume your tried manually entering the numbers and then sorted using a
helper column and RAND() and this is wasn't what suited your needs. I can't
visualise a worksheet formula solution to this so maybe this VB one. Right
click your sheet tab, view code and paste this in and run it. You could maybe
assign a button to run it or run it from tools|Macros so you can see the
worksheet populating with data and how it begins to struggle finding the
unique numbers for the last dozen or so cells.

Sub Liminal_Advertising()
Dim FillRange As Range
Set FillRange = Range("A1:H200")
For Each c In FillRange
Do
c.Value = Int((1600 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

Mike

 

[Dave]

Gents,

Apologies for the single reply to all of your excellent solutions all of
which worked flawlessly. I am most grateful.

D

 

[Rick Rothstein]

Here's one more...

Sub FillRangeWithRandomNumbers()
Dim R As Range
Dim X As Long
Dim Temp As Long
Dim RandomIndex As Long
Dim RandomNumbers() As Long
Const CellRange As String = "A1:H200"
ReDim RandomNumbers(1 To Range(CellRange).Count)
' Initialize the RandomNumbers array
For X = 1 To UBound(RandomNumbers)
RandomNumbers(X) = X
Next
' Randomize the RandomNumbers array
For X = UBound(RandomNumbers) To 1 Step -1
RandomIndex = Int((X - LBound(RandomNumbers) + 1) * _
Rnd + LBound(RandomNumbers))
Temp = RandomNumbers(RandomIndex)
RandomNumbers(RandomIndex) = RandomNumbers(X)
RandomNumbers(X) = Temp
Next
' Distribute the random numbers to the Range
X = 1
For Each R In Range(CellRange)
R.Value = RandomNumbers(X)
X = X + 1
Next
End Sub

 

[Bernd P]

Hello,

Another one which offers unique numbers as well as (optional!)
repeating numbers (up to a given limit) and optimizes for a small
count of draws in a huge range:
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd