Basic Counting Principle List on Excel

[MattH1]

Good morning,
I am looking to code a macro that would essentially count out all the possible permutations of a data set. For example:

Column A can be AA, AB, or AC.
Column B can be either 0 or 1.
Column C can be XX XY or XZ.
Column D can only be 6.

Is there any way for excel to read a sheet where it has the variables listed and spit out the permutations as such?

Column A | Column B |Column C | Column D
AA 0 XX 6
AA 0 XY 6
AA 0 XZ 6
AA 1 XX 6

And so on and so forth, going through all possible permutations. I would like this code to be editable, preferably where I can now add an AD option to column A and re-run the macro without much or any editing. Any and all ideas you have are much appreciated, I am really lost on how to even start this code. Thank you!

 

[MattH1]

I thought spacing would work, but it seemed to have failed me here.
I would like it to spit it out across each column, not all in Column A. So "AA | 0 | XX | 6". If that wasn't clear already, sorry!

 

[MickG]

Perhaps:-

[COLOR="Navy"]Sub[/COLOR] MG15Jul04
[COLOR="Navy"]Dim[/COLOR] Ray1, Ray2, Ray3
[COLOR="Navy"]Dim[/COLOR] n1, n2, n3, c
Ray1 = Array("AA", "AB", "AC")
Ray2 = Array(0, 1)
Ray3 = Array("XX", "XY", "XZ")
[COLOR="Navy"]For[/COLOR] n1 = 0 To UBound(Ray1)
    [COLOR="Navy"]For[/COLOR] n2 = 0 To UBound(Ray2)
        [COLOR="Navy"]For[/COLOR] n3 = 0 To UBound(Ray3)
            c = c + 1
            Cells(c, 1) = Ray1(n1)
            Cells(c, 2) = Ray2(n2)
            Cells(c, 3) = Ray3(n3)
            Cells(c, 4) = 6
        [COLOR="Navy"]Next[/COLOR] n3
    [COLOR="Navy"]Next[/COLOR] n2
[COLOR="Navy"]Next[/COLOR] n1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MattH1]

Thank you so much Mick that definitely seems like it works for the time being (my actual sheet has about 150 different columns so I'll just continue the basic idea. Is there a way for the Array to read a list on a sheet in Excel and not have to be populated manually? I want to have a Sheet on Excel with just the columns and if I add one more variable to the sheet the code will automatically update.

 

[MattH1]

Does anyone have any suggestions on this? I have looked around and save maybe ActiveCell might be my answer but can't get it to work.

 

[MattH1]

Dim PlanCode, Ray2, Ray3Dim n1, n2, n3, c, PlanCodeUB, Ray2UB, Ray3UB
PlanCode = Array("LCAA", "LCAB", "LCAC", "LCAL")
'PlanCode = Array(Sheets("Sheet2").Range("A:A").Value)
Ray2 = Array(0, 1)
Ray3 = Array("XX", "XY", "XZ")
Sheets("Sheet1").Select


For n1 = 0 To UBound(PlanCode)
    For n2 = 0 To UBound(Ray2)
        For n3 = 0 To UBound(Ray3)
            c = c + 1
            Cells(c, 1) = PlanCode(n1)
            Cells(c, 2) = Ray2(n2)
            Cells(c, 3) = Ray3(n3)
            Cells(c, 4) = 6
        Next n3
    Next n2
Next n1
End Sub

The 'PlanCode is what I'm trying to get working. I can't get it to select the range of all values in A that have a value in them, subject to me putting in more or less values. Even when I would set the value A1:A4, it would only give me the value A1 and wouldn't go through the rest of the permutations. Does anyone know why this is happening and how to fix it so it goes through all of the variables listed, stopping after the last one is listed (which will allow me to add more when needed).

 

[MickG]

I have some code for a limited number of columns, but to be aware !!
If you had 150 columns and 3 rows the Results list would be 150 ^3 = 3375000 rows of data.

This is based code for 5 columns of data ,Starting column "A" (alter as required)
Results sheet2, starts "A1"

[COLOR="Navy"]Sub[/COLOR] MG15Jul08
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tot [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] rCnt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 rCnt = 1
 For Ac = 1 To 5 '[COLOR="Green"][B]5 = Number of columns[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(1, Ac), Cells(Rows.Count, Ac).End(xlUp))
        rCnt = rCnt * Rng.Count
  [COLOR="Navy"]Next[/COLOR] Ac
 
s = rCnt
For Ac = 1 To 5 '[COLOR="Green"][B]5 = Number of columns[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(1, Ac), Cells(Rows.Count, Ac).End(xlUp))
        c = 1: num = s / Rng.Count: s = num: Tot = s * Rng.Count
        [COLOR="Navy"]For[/COLOR] n = 1 To rCnt
            Sheets("Sheet2").Cells(n, Ac) = Rng(c)
                [COLOR="Navy"]If[/COLOR] n Mod num = 0 [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]If[/COLOR] c Mod Rng.Count = 0 [COLOR="Navy"]Then[/COLOR] c = 0
                    c = c + 1
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick