Updating a permutation generator

[SPACKlick]

Hi there, two questions.(both highlighted in bold for TL;DR purposes

I currently have the below macro which I use to generate every permutation of 5 variables (currently all set from 1-6)

Sub Permutations()
Dim i As Integer, n As Integer, o As Integer, p As Integer, q As Integer, r As Integer
i = 1
For r = 1 To 6
For q = 1 To 6
For p = 1 To 6
For o = 1 To 6
For n = 1 To 6

Range("A" & i).Value = r
Range("B" & i).Value = q
Range("C" & i).Value = p
Range("D" & i).Value = o
Range("E" & i).Value = n

i = i + 1

Next n
Next o
Next p
Next q
Next r

End Sub

When I have more than need the variables to be different numerals I can easily edit to make that happen, however if I want more than 5 variables or significantly more than 6 options for each variable this macro takes ages to generate it, is there a streamlining option I've missed?

Also, if I want the variable output to be letters or labels rather than numbers, or if I need it to be dates and times is there an easy edit for doing just that? at the moment I'm using find replace on my numerals and manually editing but it's quite time inefficient and I was hoping to be able to build soemthing into the macro.

 

[JackDanIce]

A slight increase in speed would be to turn off the screen updating and calculation e.g.

Sub Permutations()
Dim i As Integer, n As Integer, o As Integer, p As Integer, q As Integer, r As Integer

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
    
i = 1
For r = 1 To 6
    For q = 1 To 6
        For p = 1 To 6
            For o = 1 To 6
                For n = 1 To 6
                    Range("A" & i).Value = r
                    Range("B" & i).Value = q
                    Range("C" & i).Value = p
                    Range("D" & i).Value = o
                    Range("E" & i).Value = n
                    i = i + 1
                Next n
            Next o
        Next p
    Next q
Next r
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

 

[syntaxed]

shg posted some great code for permutations here:
http://www.mrexcel.com/forum/showthread.php?t=530891&page=2

 

[SPACKlick]

Hey Jack, your code gives me, when trying with 6 variables, an error
It gets to row 32767 and then the following message pops up

Runtime error '6': Overflow.

When I hit debug, the highlighted line is i = i+1, is there some cap at 2^15 on macro integers? If so I will just avoid using it where the number of permutations is greater than 2^15

@ Syntaxed, I'm not sure I understand SHG's code, I'll need to spend some time looking at it.

 

[SPACKlick]

Ok, New Problem. With the permutations generated I need to remove translation duplicates (Rotation and reflection)
I have a way of converting each permutation to a number
p1=(2^A+3^B+5^C+7^D+11^E+13^F)
And converting to the reflections and rotations.
t1=(2^C+3^B+5^A+7^F+11^E+13^D)
t2=(2^D+3^E+5^F+7^A+11^B+13^C)
t3=(2^F+3^E+5^D+7^C+11^B+13^A)
I need to add an if loop into the generator that checks if any of the 3 translations (reflect in x, reflect in y, rotate 180) are the same as any previous permutation and

IF OR(t1=p1(for i=1 to i-1), t2=p1(for i=1 to i-1), t3=p1(for i=1 to i-1),)
next n
ELSE
i = i + 1
next n

such that repeated permutations get overwritten. But everything I write doesn't work...

Thoughts?

 

[Ruddles]

Hey Jack, your code gives me, when trying with 6 variables, an error
It gets to row 32767 and then the following message pops up

Runtime error '6': Overflow.

Integer types are 16-bit, so can only go from -32768 to 32767. Use the 32-bit type Long which goes from +/- 2.1 billion.

 

[Ruddles]

Ok, New Problem. With the permutations generated I need to remove translation duplicates (Rotation and reflection)

I think this is what you need:-

For r = 1 To [COLOR=red]2
[/COLOR]    For q = [COLOR=red]r+[/COLOR]1 To [COLOR=red]3
[/COLOR]        For p = [COLOR=red]q+[/COLOR]1 To [COLOR=red]4
[/COLOR]            For o = [COLOR=red]p+[/COLOR]1 To [COLOR=red]5
[/COLOR]                For n = [COLOR=red]o+[/COLOR]1 To [COLOR=red]6
[/COLOR]                    Range("A" & i).Value = r
                    Range("B" & i).Value = q
                    Range("C" & i).Value = p
                    Range("D" & i).Value = o
                    Range("E" & i).Value = n
                    i = i + 1
                Next n
            Next o
        Next p
    Next q
Next r