Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB combination code
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combination Sum | Excel Worksheet Functions | |||
combination | Excel Discussion (Misc queries) | |||
Combination | Charts and Charting in Excel | |||
Combination Charts | Charts and Charting in Excel | |||
Indirect in combination with ROW | Excel Worksheet Functions |