VB combination code

[margie]

I am currently writing a scheduling program in VB. The user will enter
several orders let's call them A, B, C, D in 4 rows in the worksheet.
From these orders I would like the code to come up with all
combinations of those 4 orders. For example,

A B C D
A B D C
A C B D
A C D B
B A C D
B A D C

and so on.
I came up with something but it didn't go through all the combinations.


Can anyone help me?

 

[BrianB]

You need to know the number of items in advance. Using your exampl
there are 256 possibilities.

'------------------------------------------------------------
Sub TEST()
Dim MyRange As Range
Dim ToRow As Long
'------------------
Set MyRange = Selection '(eg.4 cells A1:A4)
ToRow = 1
For n1 = 1 To 4
For n2 = 1 To 4
For n3 = 1 To 4
For n4 = 1 To 4
ActiveSheet.Cells(ToRow, 2).Value
MyRange.Cells(n1, 1).Value
ActiveSheet.Cells(ToRow, 3).Value
MyRange.Cells(n2, 1).Value
ActiveSheet.Cells(ToRow, 4).Value
MyRange.Cells(n3, 1).Value
ActiveSheet.Cells(ToRow, 5).Value
MyRange.Cells(n4, 1).Value
ToRow = ToRow + 1
Next
Next
Next
Next
End Sub
'---------------------------------------------

 

[Tom Ogilvy]

Actually

=combin(4,4) reveals the obvious. There is only one unique combination of
the letters A B C D

Now if we are talking permutations as she appears to describe, then

see John Walkenbach's site for code to generate the 24 possible
permutations.

=PERMUT(4,4)

http://j-walk.com/ss/excel/tips/tip46.htm

Your code repeats characters - AAAA would be a possible result - which may
or may not be what she wants, but nothing with repeating characters was
shown in the example.

 

[margie]

Thank you for your help. I was confused. It was permutations no
combinations. But I am still stuck. The code you led me to permutes
string of characters. My program is taking for example, order A i
cell A1, and order B in cell A2, and order C in cell A3 and so on. S
it should be able to read the one value in each cell and switch the
around to make all the permutations. The output should also be i
separate rows. So that in excel it looks like this:

A B
B A
C D
D C

Any ideas on that? This program is supposed to schedule jobs on
machine. I have very little background in programming. I am actuall
an industrial engineer. So this is quite a task

 

[Tom Ogilvy]

Put you job names in A1:A4

example

Job1
Job2
Job3
Job4

Put this in a general module:

Dim CurrentCol
Dim JobList(1 To 4) As String

Sub GetString()
Dim InString As String
InString = "1234"
JobList(1) = Range("A1").Value
JobList(2) = Range("A2").Value
JobList(3) = Range("A3").Value
JobList(4) = Range("A4").Value
If Len(InString) < 2 Then Exit Sub
If Len(InString) >= 5 Then
MsgBox "Too many permutations!"
Exit Sub
Else
ActiveSheet.Columns(2).Resize(, 255).Clear
CurrentCol = 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, sres As String
j = Len(y)
If j < 2 Then
sres = x & y
For rw = 1 To Len(x & y)
Cells(rw, CurrentCol) = JobList(CLng(Mid(x & y, rw, 1)))
Next
CurrentCol = CurrentCol + 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


Run getstring
and it will put your permutations in column A to Column X

 

[margie]

Thanks Tom