VB combination code
 

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?


---
Message posted from http://www.ExcelForum.com/

VB combination code
 

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
'---------------------------------------------

--
Message posted from http://www.ExcelForum.com

VB combination code
 

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.

--
Regards,


"BrianB " wrote in message
...
You need to know the number of items in advance. Using your example
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
'----------------------------------------------


---
Message posted from http://www.ExcelForum.com/

VB combination code
 

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

--
Message posted from http://www.ExcelForum.com

VB combination code
 

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

--
Regards,
Tom Ogilvy




"margie " wrote in message
...
Thank you for your help. I was confused. It was permutations not
combinations. But I am still stuck. The code you led me to permutes a
string of characters. My program is taking for example, order A in
cell A1, and order B in cell A2, and order C in cell A3 and so on. So
it should be able to read the one value in each cell and switch them
around to make all the permutations. The output should also be in
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 a
machine. I have very little background in programming. I am actually
an industrial engineer. So this is quite a task.


---
Message posted from http://www.ExcelForum.com/

VB combination code
 

Thanks Tom

--
Message posted from http://www.ExcelForum.com