At www.cpearson.com/Excel/ShuffleArray.aspx I have a function called
ShuffleArray which scrambles an array in random order. You can wrap
this up in a function to randomly scramble a string of characters:

Function ShuffleChars(S As String) As Variant
' Reorders characters of S in random order.
' Calls ShuffleArray.
Dim Arr() As Variant
Dim N As Long
Dim T As String

If Len(S) = 0 Then
ShuffleChars = CVErr(xlErrValue)
Exit Function
End If
ReDim Arr(1 To Len(S))
For N = 1 To Len(S)
Arr(N) = Mid(S, N, 1)
Next N
Arr = ShuffleArray(Arr)
For N = 1 To UBound(Arr)
T = T & Arr(N)
Next N
ShuffleChars = T
End Function


This calls ShuffleArray, shown he

Function ShuffleArray(InArray() As Variant) As Variant()
Dim N As Long
Dim L As Long
Dim Temp As Variant
Dim J As Long
Dim Arr() As Variant

Randomize
L = UBound(InArray) - LBound(InArray) + 1
ReDim Arr(LBound(InArray) To UBound(InArray))
For N = LBound(InArray) To UBound(InArray)
Arr(N) = InArray(N)
Next N
For N = LBound(InArray) To UBound(InArray)
J = Int((UBound(InArray) - LBound(InArray) + 1) * _
Rnd + LBound(InArray))
If N < J Then
Temp = Arr(N)
Arr(N) = Arr(J)
Arr(J) = Temp
End If
Next N
ShuffleArray = Arr
End Function


You can call ShuffleChars from a worksheet cell with

=ShuffleChars(A1) where A1 contains the characters to scramble.

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





On Sat, 11 Apr 2009 11:54:13 +0930, "RobN" wrote:

I have a 6 digit number (1-6) and no number is repeated. Is there a formula
or other VBA function that will permutate that number (ie scramble or
interchange the digits), either once off or, preferably, rearrange them in
every possible way.

Rob