# Solved How do I create a macro that makes permutation tables?

April 2, 2017 at 05:00:20
Specs: Macintosh
 Hello, I am someone with limited programming knowledge, but I urgently need to generate a table of permutations. Specifically, I need to make a table that has (in this case) 5 columns, each representing a "trait" that can each be at a value of "high", "medium" or "low". I need the table to consist of each possible combination of traits (for example, high high high high high).To do this, it would be great to use an excel macro that could be easily modifiable to edit the number of states each trait exists in, as well as change the number of traits. I have tried doing this by using the following code (which I got from another help forum), but I get a compile error.< pre > Sub PermutationsTable()'First, define your array of valuesDim MyArray(1)MyArray(0) = "L"MyArray(1) = "M"MyArray(2) = "H"Col = 1'Now, "print" the combinations by looping through your arrayFor g = 0 To 1For h = 0 To 1For i = 0 To 1For j = 0 To 1For k = 0 To 1****Cells(1, Col) = MyArray(g)****Cells(2, Col) = MyArray(h)****Cells(3, Col) = MyArray(i)****Cells(4, Col) = MyArray(j)****Cells(5, Col) = MyArray(k)****Col = Col + 1Next kNext jNext iNext hNext gEnd Sub Thank you!

See More: How do I create a macro that makes permutation tables?

#1
April 2, 2017 at 06:16:22
 Well, first let's fix the macro you posted, then we'll modify it to give you what you want.1 - Posting the code: There should not be spaces within the pre tag operator. If you click the pre tag symbol above the Post Reply box, you'll get the correct format.2 - Obviously, the asterisks need to go away. e.g.`****Cells(1, Col) = MyArray(g)`becomes`Cells(1, Col) = MyArray(g)`3 - Next, We need to fix this:```Dim MyArray(1) MyArray(0) = "L" MyArray(1) = "M" MyArray(2) = "H"```When you Dim an Array as (1), you only get 2 elements - 0 and 1. As soon as the code tries to set the 3rd element MyArray(2) you are going to get a subscript out of range error.`Dim MyArray(1)`becomes`Dim MyArray(2)`4 - Since you have 3 elements in the array, you need to expand the loop. e.g. `For g = 0 To 1`becomes`For g = 0 To 2`The same holds for h:kOK, once those changes are made, that is going to give you all the permutations of L, M and H spread out across A1:II5, with each permutation in a Column. Of course, that is not what you want. I believe that you need the permutation output in rows.In any case, here is the "fixed" original code just so that you can see the changes:```Sub PermutationsTable() 'First, define your array of values Dim MyArray(2) MyArray(0) = "L" MyArray(1) = "M" MyArray(2) = "H" Col = 1 'Now, "print" the combinations by looping through your array For g = 0 To 2 For h = 0 To 2 For i = 0 To 2 For j = 0 To 2 For k = 0 To 2 Cells(1, Col) = MyArray(g) Cells(2, Col) = MyArray(h) Cells(3, Col) = MyArray(i) Cells(4, Col) = MyArray(j) Cells(5, Col) = MyArray(k) Col = Col + 1 Next k Next j Next i Next h Next g End Sub ```Now, since you said you have 5 columns, we need to transpose the table so that the permutations of L, M and H end up in rows under the trait. We do that by incrementing the Row argument, not the Column argument, in the loop.If you start with this table, the following code will list each permutation horizontally in Row 2:244``` A B C D E 1 Trait 1 Trait 2 Trait 3 Trait 4 Trait 5 ``````Sub PermutationsTable() 'First, define your array of values Dim MyArray(2) MyArray(0) = "L" MyArray(1) = "M" MyArray(2) = "H" rw = 2 'Now, "print" the combinations by looping through your array For g = 0 To 2 For h = 0 To 2 For i = 0 To 2 For j = 0 To 2 For k = 0 To 2 Cells(rw, 1) = MyArray(g) Cells(rw, 2) = MyArray(h) Cells(rw, 3) = MyArray(i) Cells(rw, 4) = MyArray(j) Cells(rw, 5) = MyArray(k) rw = rw + 1 Next k Next j Next i Next h Next g End Sub```For now, that should get you what you need. In a separate response I'll offer a solution for varying the number of Traits and States, as that adds another layer of complexity to the matter. I don't have time to work on that right now. Obviously, manually changing the loops in the code above will accomplish that goal, but I'm sure that there is a better way.message edited by DerbyDad03