Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
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
For j = 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
For r = 1 To RCount
For c = 1 To CCount
i = i + 1
V(r, c) = ValArray(2, i)
Next c
Next r
RangeRandomize = V
End Function
The code is very similar to that for the RandomIntegers function.
Figure 11-18 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.

Search JabSto ::

Custom Search