Use this:
Sub FillRand() Dim nums() As Integer Dim maxval As Integer Dim
nrows As Integer, ncols As Integer Dim j As Integer, k As Integer Dim
Ptr As Integer Randomize Set s = Selection maxval = s.Cells.Count
nrows = s.Rows.Count ncols = s.Columns.Count ReDim nums(maxval, 2)
'Fill the initial array For j = 1 To maxval nums(j, 1) = j
nums(j, 2) = Int((Rnd * maxval) + 1) Next j 'Sort the array based on
the random numbers For j = 1 To maxval - 1 Ptr = j For k =
j + 1 To maxval If nums(Ptr, 2) > nums(k, 2) Then Ptr = k
Next k If Ptr <> j Then k = nums(Ptr, 1)
nums(Ptr, 1) = nums(j, 1) nums(j, 1) = k k = nums(Ptr,
2) nums(Ptr, 2) = nums(j, 2) nums(j, 2) = k End
If Next j 'Fill in the cells Ptr = 0 For j = 1 To nrows
For k = 1 To ncols Ptr = Ptr + 1 s.Cells(j, k) =
nums(Ptr, 1) Next k Next jEnd SubThis macro uses a two-dimensional
array (nums) to figure out which numbers to use and the order in which they
should be used. Near the beginning of the macro the array is filled with a
static number (1 through the number of cells) and a random number between 1
and the number of cells. This second number is then used to sort the array.
Once the array is stored, it is a simple matter to place the original
numbers in the cells.
By the way, the reason a two-dimensional array is used is because the Rnd
function that VBA uses to generate random numbers can return duplicate
values. Thus, even through the second dimension of the array can have
duplicates in it, when the array is finally sorted, the first dimension will
not have duplicates.
To use the macro, start by selecting the cells you want to have filled with
sequential values in a random order. When you run the macro, that range is
filled. For instance, if you select ten cells and then run the macro, then
those cells are filled with the numbers 1 through 10, in random order.
Jack Sons
