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" <
[email protected]>
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