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

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

Post a comment