## Randomizing a range

The RANGERANDOMIZE function, which follows, accepts a range argument and returns an array that consists of the input range — in random order:

Function RANGERANDOMIZE(rng)

Dim V() As Variant, ValArray() As Variant

Dim CellCount As Double

Dim i As Integer, j As Integer

Dim r As Integer, c As Integer

Dim Tempi As Variant, Temp2 As Variant

Dim RCount As Integer, CCount As Integer

Randomize

' Return an error if rng is too large CellCount = rng.Count If CellCount > 1000 Then

RANGERANDOMIZE = CVErr(xlErrNA) Exit Function End If

' Assign variables

RCount = rng.Rows.Count CCount = rng.Columns.Count ReDim V(1 To RCount, 1 To CCount) ReDim ValArray(1 To 2, 1 To CellCount)

' Fill ValArray with random numbers

' and values from rng For i = 1 To CellCount ValArray(1, i) = Rnd ValArray(2, i) = rng(i) Next i

' Sort ValArray by the random number dimension For i = 1 To CellCount

If ValArray(1, i) > ValArray(1, j) Then Temp1 = ValArray(1, j) Temp2 = ValArray(2, j) ValArray(1, j) = ValArray(1, i) ValArray(2, j) = ValArray(2, i) ValArray(1, i) = Temp1 ValArray(2, i) = Temp2 End If

Next j Next i

' Put the randomized values into the V array i = 0

RANGERANDOMIZE = V End Function

The code is very similar to that for the RANDOMINTEGERS function. Figure 11-12 shows the function in use. The array formula in B2:B11 is:

{=RANGERANDOMIZE(A2:A11)}

This formula returns the contents of A2:A11, but in random order.

 X A B c -71 1 Original Randomized 2 1 9 3 2 10 4 3 3 5 4 2 6 5 1 7 6 6 8 7 4 9 0 0 10 9 7 11 1Ü 5 12 1 >ir M ► »I \ Sheet "\sheet2 /|l|

Figure 11-12: The RANGERANDOMIZE function returns the contents of a range, in random order.

0 0