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

Well, I just happen to have one...
'---------------------------
' Apr 14, 2002 - Created - Jim Cone - Portland, Oregon USA
' Scrambles the order of the data in a single-cell.
' If second argument evaluates to True, then scrambles each time sheet is calculated.
' Apr 27, 2002 - Removed check for length of Everytime variable as causing error.
' If user text is nothing then provide message. Added error handler.
' Apr 20, 2003 - Made all arguments optional and modified code accordingly.
' Nov 02, 2003 - Simplified Application.Volatile. Added check for user entry error.
' Sep 01, 2006 - Moved TypeName(UserText) line above Application.Volatile.
' Dec 13, 2008 - Changed concept. Now each word is individually scrambled instead
' of scrambling the entire string as a unit. Calls CountIn function.
Function SCRAMBLE(Optional ByRef UserText As Variant, _
Optional ByRef Everytime As Variant) As String
On Error GoTo Scorched
Dim i As Long
Dim j As Long
Dim Num As Long
Dim SpaceCount As Long
Dim NewPosition As Long
Dim Temp As String
Dim strWord As String

If IsMissing(UserText) Then
SCRAMBLE = "No data"
Exit Function
ElseIf IsError(UserText) Then
'No quotes automatically generates an error from the worksheet.
SCRAMBLE = "Error - try adding quote marks around your entry."
Exit Function
ElseIf TypeName(UserText) = "Range" Then
UserText = UserText(1).Value
End If
Application.Volatile (Not IsMissing(Everytime))
UserText = Application.Trim(UserText)

If Len(UserText) 0 Then
SpaceCount = COUNTIN(UserText, " ")
For i = 1 To SpaceCount + 1 'number of words in text
strWord = ExtractString(UserText, i, " ")
Num = Len(strWord)
Randomize
For j = 1 To Num
If Num = 1 Then
Exit For
Else
Temp = Mid$(strWord, j, 1)
'CLng rounds, so could exceed length of text
NewPosition = Int(Num * Rnd + 1)
Mid$(strWord, j, 1) = Mid$(strWord, NewPosition, 1)
Mid$(strWord, NewPosition, 1) = Temp
End If
Next
SCRAMBLE = SCRAMBLE & " " & strWord
Temp = vbNullString
Next ' i
SCRAMBLE = VBA.Trim$(SCRAMBLE)
Else
SCRAMBLE = "No data" 'Can result from entering ""
End If
Exit Function
Scorched:
SCRAMBLE = "Error " & Err.Number
End Function
'--------------

'Returns the number of times strChar appears in InputText
'Called by Scramble function. Jim Cone - Portland, Oregon USA
'Nov 09, 2004 - Added "\ Len(strChar)" code to handle delimiters 1 character.
'Dec 13, 2008 - Change InputText to Variant from String so as to work with Scramble function.
Function COUNTIN(ByRef InputText As Variant, ByRef strChars As String) As Long
On Error GoTo LostCount
If Len(strChars) Then
COUNTIN = (Len(InputText) - Len(Application.Substitute(InputText, _
strChars, vbNullString))) \ Len(strChars)
End If
Exit Function
LostCount:
Beep
COUNTIN = 0
End Function
--
Jim Cone
Portland, Oregon USA



"RobN"
wrote in message
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

MORE INFO...
The function, as written calls the ExtractString function which I forgot to post.
The missing function enables SCRAMBLE to used in xl97.
The function is not needed(for later xl versions), if you just replace the following four lines of code...
'---
If Len(UserText) 0 Then
SpaceCount = COUNTIN(UserText, " ")
For i = 1 To SpaceCount + 1 'number of words in text
strWord = ExtractString(UserText, i, " ")

With...

If Len(UserText) 0 Then
SpaceCount = COUNTIN(UserText, " ")
For i = 0 To SpaceCount
strWord = VBA.Split(UserText, " ")(i)
--
Jim Cone
Portland, Oregon USA

Hi Jim,

I'm using Vs2007

Can't get this to work even with the modification. Always goes to:
SCRAMBLE = "No data"
Exit Function

I'm not too cluey wuth this sort of stuff, but the code you sent seems to be
for text, not numbers, or doesn't that matter. (Or, don't I know what I'm
talking about? - the more likely option!)

Furthermore, in what cell do I need to put the number?

Rob


"Jim Cone" wrote in message
...
MORE INFO...
The function, as written calls the ExtractString function which I forgot
to post.
The missing function enables SCRAMBLE to used in xl97.
The function is not needed(for later xl versions), if you just replace the
following four lines of code...
'---
If Len(UserText) 0 Then
SpaceCount = COUNTIN(UserText, " ")
For i = 1 To SpaceCount + 1 'number of words in text
strWord = ExtractString(UserText, i, " ")

With...

If Len(UserText) 0 Then
SpaceCount = COUNTIN(UserText, " ")
For i = 0 To SpaceCount
strWord = VBA.Split(UserText, " ")(i)
--
Jim Cone
Portland, Oregon USA

Hi,

Maybe a little simpler. ALT+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code below in.

call with
=Mix(A1)

or

=Mix(123456) or =Mix(abcde)

Function Mix(Utext As Variant) As String
Dim i As Long
Dim NewPos As Long
Dim Temp As String
For i = 1 To Len(Utext)
Temp = Mid$(Utext, i, 1)
NewPos = Int(Len(Utext) * Rnd + 1)
Mid$(Utext, i, 1) = Mid$(Utext, NewPos, 1)
Mid$(Utext, NewPos, 1) = Temp
Next
Mix = Utext
End Function

Mike

"RobN" wrote:

Hi Jim,

I'm using Vs2007

Can't get this to work even with the modification. Always goes to:
SCRAMBLE = "No data"
Exit Function

I'm not too cluey wuth this sort of stuff, but the code you sent seems to be
for text, not numbers, or doesn't that matter. (Or, don't I know what I'm
talking about? - the more likely option!)

Furthermore, in what cell do I need to put the number?

Rob


"Jim Cone" wrote in message
...
MORE INFO...
The function, as written calls the ExtractString function which I forgot
to post.
The missing function enables SCRAMBLE to used in xl97.
The function is not needed(for later xl versions), if you just replace the
following four lines of code...
'---
If Len(UserText) 0 Then
SpaceCount = COUNTIN(UserText, " ")
For i = 1 To SpaceCount + 1 'number of words in text
strWord = ExtractString(UserText, i, " ")

With...

If Len(UserText) 0 Then
SpaceCount = COUNTIN(UserText, " ")
For i = 0 To SpaceCount
strWord = VBA.Split(UserText, " ")(i)
--
Jim Cone
Portland, Oregon USA

Hi Mike,

Thanks for that. Works perfectly for a single scramble.
I found some code on the internet that will list every possible permutation
, so I'm happy.

Rob


"Mike H" wrote in message
...
Hi,

Maybe a little simpler. ALT+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code below in.

call with
=Mix(A1)

or

=Mix(123456) or =Mix(abcde)

Function Mix(Utext As Variant) As String
Dim i As Long
Dim NewPos As Long
Dim Temp As String
For i = 1 To Len(Utext)
Temp = Mid$(Utext, i, 1)
NewPos = Int(Len(Utext) * Rnd + 1)
Mid$(Utext, i, 1) = Mid$(Utext, NewPos, 1)
Mid$(Utext, NewPos, 1) = Temp
Next
Mix = Utext
End Function

Mike

"RobN" wrote:

Hi Jim,

I'm using Vs2007

Can't get this to work even with the modification. Always goes to:
SCRAMBLE = "No data"
Exit Function

I'm not too cluey wuth this sort of stuff, but the code you sent seems to
be
for text, not numbers, or doesn't that matter. (Or, don't I know what I'm
talking about? - the more likely option!)

Furthermore, in what cell do I need to put the number?

Rob


"Jim Cone" wrote in message
...
MORE INFO...
The function, as written calls the ExtractString function which I
forgot
to post.
The missing function enables SCRAMBLE to used in xl97.
The function is not needed(for later xl versions), if you just replace
the
following four lines of code...
'---
If Len(UserText) 0 Then
SpaceCount = COUNTIN(UserText, " ")
For i = 1 To SpaceCount + 1 'number of words in text
strWord = ExtractString(UserText, i, " ")

With...

If Len(UserText) 0 Then
SpaceCount = COUNTIN(UserText, " ")
For i = 0 To SpaceCount
strWord = VBA.Split(UserText, " ")(i)
--
Jim Cone
Portland, Oregon USA

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

Thanks Chip.

Rob

"Chip Pearson" wrote in message
...
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

My question concerns a similar issue regarding the PERMUTATION command.
Specifically is it possible to use this command with multiple "R's?" The
first argument is N and represents the number of choices. The second argument
represents the types of choices or groupings. In some cases the R can have
several values, such as R1=4, R2=3. In these cases I have found I can only
compute the permutation by using the mathematical formula.
--
Jim


"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