MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Permutations


Posted by Raj on July 08, 2001 9:05 AM

Hi,
I desperately need to know if permutations on a number is possible with VB in Excel. All I need to do is have a 4 digit number input ( eg. 1234 ) , and I want the program to list the possible combinations of 1234. In this case it should be 24 different combinations. Is this easy to do ?

Thank you. Appreaciate your help.


Posted by Ivan F Moala on July 08, 2001 1:17 PM

This routine from John Walkenback - GIVE CREDIT to
him

Dim CurrentRow

Sub GetString()
Dim InString As String
InString = InputBox("Enter text to permute:")
If Len(InString) < 2 Then Exit Sub
If Len(S) >= 8 Then
MsgBox "Too many permutations!"
Exit Sub
Else
ActiveSheet.Columns(1).Clear
CurrentRow = 1
Call GetPermutation("", InString)
End If
End Sub

Sub GetPermutation(x As String, y As String)
' The source of this algorithm is unknown
Dim i As Integer, j As Integer
j = Len(y)
If j < 2 Then
Cells(CurrentRow, 1) = x & y
CurrentRow = CurrentRow + 1
Else
For i = 1 To j
Call GetPermutation(x + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next
End If
End Sub

Posted by Raj on July 10, 2001 10:56 PM


Thanks Ivan with the Permutations routine from John. Actually it has only one problem eg.
If I enter the number '6000' , it should return the following values '6000,0600,0060,0006' but it gives me 24 values like '6,0,600 etc. Its perfect if I enter a 4 digit number with no repeats eg 1234. I forgot to say that I only want to permute on 4 digit numbers only.
I can still use it because I can edit the results in the worksheet.

Thanks a lot.

regards.

Posted by Ivan F Moala on July 11, 2001 3:42 AM

You can always add routine to format it as;
Selection.NumberFormat = "0000"

Ivan