Permutation List Code

Hi,

I would like to know how to populate 27 rows with the 27 permutations of a,b,c (in different columns of each row). So it would look something like
a,a,a
b,a,a
b,b,a
b,b,b
a,b,b
c,a,a
... to 27th row

This would probably require a triple nested loop. This code in javascript does the trick, but I am unable to translate something like it into vba. Here's the javascript code if it helps:

var set = ['a', 'b', 'c'];
var result = [];

var elt = ";
for (var i = 0 < set.length; i++) {
for (var j = 0; j < set.length; j++) {
for (var k = 0; k< set.length; k++) {
elt = set[i] + set[j] + set[k];
result.push(elt);
}
}
}

Thanks for your help!

Not the most efficient, but close to your example

Code:

---

Option Explicit
Option Base 1
Sub ABC()
    Dim i1 As Long, i2 As Long, i3 As Long, iOut As Long
    Dim aABC As Variant
   
    aABC = Array("a", "b", "c")
   
    iOut = 1
   
    Application.ScreenUpdating = False
   
    For i1 = LBound(aABC) To UBound(aABC)
        For i2 = LBound(aABC) To UBound(aABC)
            For i3 = LBound(aABC) To UBound(aABC)
                ActiveSheet.Cells(iOut, 1).Value = aABC(i1)
                ActiveSheet.Cells(iOut, 2).Value = aABC(i2)
                ActiveSheet.Cells(iOut, 3).Value = aABC(i3)
                iOut = iOut + 1
            Next i3
        Next i2
    Next i1

    Application.ScreenUpdating = True
End Sub

---

Hi

Just for fun, a one-liner (specific to this problem):

Code:

---

Sub Permut()

Range("C2").Resize(27, 3).Value = Application.Index(Array("a", "b", "c"), Evaluate("if(row(1:27),mod(int((row(1:27)-1)/{9,3,1}),3)+1)"))
End Sub

---

another one:

Code:

---

Sub M_snb()
    Cells(1, 5).Resize(27) = [index(char(97+int((row(1:27)-1)/9)) & char(97+int(mod((row(1:27)-1),9)/3))& char(97+mod(row(1:27)-1,3)),)]
End Sub

---

or even:

Code:

---

Sub Permuta()
    Range("C2").Resize(27, 3).Value = [Index(char(96+mod(int((row(1:27)-1)/{9,3,1}),3)+1),)]
End Sub

---

Thanks everyone, I really appreciate it!