VBA to Make Combinations with Dynamic Data

T

thenapolitan

Board Regular
Joined
Sep 5, 2014
Messages
52
Hey Guys,

I've been racking my brain and can't seem to figure out the coding logic for what I'm doing. Basically, what I want to do is get all the combinations for a subset of parts to eventually come up with a gap/tolerance stack equation (not automated, yet).

I ask the user how many unique parts exist (in the example below, the user entered 3, but could be any number). I then ask how many duplicates of each (in this case 3 of part 1, 3 of part 2, and 2 of part 3, but again, these numbers could vary). It populates TABLE 1 below.

I populate a 2D array like this, but I'm not sure this is really how it should be done.
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]part[/TD]
[TD="width: 64"]# of parts[/TD]
[TD="width: 64"]dims[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Now, I want to make a matrix to show all combinations of those parts. There should be 18 combinations (3*3*2). And I want the matrix to look like TABLE 2.

I just can't wrap my head around how to get this to work for any number of unique parts, with any number of duplicates and any number of dimensions.

Any ideas?

Thanks,

Chris



TABLE 1
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: right"]Part:[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]Number of Parts:[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Number of Dimensions:[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Dim 1[/TD]
[TD="align: center"]Dim 2[/TD]
[TD="align: center"]Dim 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 1 of 1:[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 2 of 1:[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 3 of 1:[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part:[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]Number of Parts:[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Number of Dimensions:[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Dim 1[/TD]
[TD="align: center"]Dim 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 1 of 2:[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 2 of 2:[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 3 of 2:[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part:[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]Number of Parts:[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Number of Dimensions:[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Dim 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 1 of 3:[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Part 2 of 3:[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



TABLE 2
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Combo #[/TD]
[TD]Part 1[/TD]
[TD]Part 2[/TD]
[TD]Part 3[/TD]
[TD]Part 1 Dim 1[/TD]
[TD]Part 1 Dim 2[/TD]
[TD]Part 1 Dim 3[/TD]
[TD]Part 2 Dim 1[/TD]
[TD]Part 2 Dim 2[/TD]
[TD]Part 3 Dim 1[/TD]
[TD]Gap Formula[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]321[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Did you see the second tab where it gets done programmatically instead of through functions?
 
Upvote 0
shg said:
Did you see the second tab where it gets done programmatically instead of through functions?
Click to expand...

Yes, but it does not give the option to programmatically use more or less than the specified columns.

I think I am going to programmatically populated the FORMULAS side and see if I can make it work that way using the combination. Will definitely post results.

Ideally, I'd like to come up with a recursive function that will do all of this for an infinite number of cases. Here is my case statement right now (only works up to case 10). For each case, all it is is the addition of another For-to loop and an extra array. Kind of hefty without recursion. Just can't wrap my head around recursion though....

Code: 
Sub CartesianProduct(MatrixSheet As Worksheet)
    Dim startrange As range


    Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer, g As Integer, h As Integer, i As Integer, j As Integer
    Dim x As Integer, z As Integer


    x = Worksheets("Sheet1").range("D2")
    z = 0


    Select Case x
        Case 1
            range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            array1 = [range1]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                z = z + 1
                startrange.range("C2").Offset(z, 0).Value = array1(a, 1)
            Next a
        Case 2
            range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            range2 = Application.InputBox(Prompt:="Please Select Range 2", Type:=8)
            
            array1 = [range1]
            array2 = [range2]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                For b = 1 To UBound(array2)
                    z = z + 1
                    startrange.Offset(z, 0).Value = array1(a, 1)
                    startrange.Offset(z, 1).Value = array1(b, 1)
                Next b
            Next a
        Case 3
            range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            range2 = Application.InputBox(Prompt:="Please Select Range 2", Type:=8)
            range3 = Application.InputBox(Prompt:="Please Select Range 3", Type:=8)
            
            array1 = [range1]
            array2 = [range2]
            array3 = [range3]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                For b = 1 To UBound(array2)
                    For c = 1 To UBound(array3)
                        z = z + 1
                        startrange.Offset(z, 0).Value = array1(a, 1)
                        startrange.Offset(z, 1).Value = array1(b, 1)
                        startrange.Offset(z, 2).Value = array1(c, 1)
                    Next c
                Next b
            Next a
        Case 4
            range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            range2 = Application.InputBox(Prompt:="Please Select Range 2", Type:=8)
            range3 = Application.InputBox(Prompt:="Please Select Range 3", Type:=8)
            range4 = Application.InputBox(Prompt:="Please Select Range 4", Type:=8)
            
            array1 = [range1]
            array2 = [range2]
            array3 = [range3]
            array4 = [range4]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                For b = 1 To UBound(array2)
                    For c = 1 To UBound(array3)
                        For d = 1 To UBound(array4)
                            z = z + 1
                            startrange.Offset(z, 0).Value = array1(a, 1)
                            startrange.Offset(z, 1).Value = array1(b, 1)
                            startrange.Offset(z, 2).Value = array1(c, 1)
                            startrange.Offset(z, 3).Value = array1(d, 1)
                        Next d
                    Next c
                Next b
            Next a
        Case 5
            range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            range2 = Application.InputBox(Prompt:="Please Select Range 2", Type:=8)
            range3 = Application.InputBox(Prompt:="Please Select Range 3", Type:=8)
            range4 = Application.InputBox(Prompt:="Please Select Range 4", Type:=8)
            range5 = Application.InputBox(Prompt:="Please Select Range 5", Type:=8)


            array1 = [range1]
            array2 = [range2]
            array3 = [range3]
            array4 = [range4]
            array5 = [range5]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                For b = 1 To UBound(array2)
                    For c = 1 To UBound(array3)
                        For d = 1 To UBound(array4)
                            For e = 1 To UBound(array4)
                                z = z + 1
                                startrange.Offset(z, 0).Value = array1(a, 1)
                                startrange.Offset(z, 1).Value = array1(b, 1)
                                startrange.Offset(z, 2).Value = array1(c, 1)
                                startrange.Offset(z, 3).Value = array1(d, 1)
                                startrange.Offset(z, 3).Value = array1(e, 1)
                            Next e
                        Next d
                    Next c
                Next b
            Next a
        Case 6
            range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            range2 = Application.InputBox(Prompt:="Please Select Range 2", Type:=8)
            range3 = Application.InputBox(Prompt:="Please Select Range 3", Type:=8)
            range4 = Application.InputBox(Prompt:="Please Select Range 4", Type:=8)
            range5 = Application.InputBox(Prompt:="Please Select Range 5", Type:=8)
            range6 = Application.InputBox(Prompt:="Please Select Range 6", Type:=8)


            array1 = [range1]
            array2 = [range2]
            array3 = [range3]
            array4 = [range4]
            array5 = [range5]
            array6 = [range6]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                For b = 1 To UBound(array2)
                    For c = 1 To UBound(array3)
                        For d = 1 To UBound(array4)
                            For e = 1 To UBound(array4)
                                For f = 1 To UBound(array4)
                                    z = z + 1
                                    startrange.Offset(z, 0).Value = array1(a, 1)
                                    startrange.Offset(z, 1).Value = array1(b, 1)
                                    startrange.Offset(z, 2).Value = array1(c, 1)
                                    startrange.Offset(z, 3).Value = array1(d, 1)
                                    startrange.Offset(z, 3).Value = array1(e, 1)
                                    startrange.Offset(z, 3).Value = array1(f, 1)
                                Next f
                            Next e
                        Next d
                    Next c
                Next b
            Next a
        Case 7
            range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            range2 = Application.InputBox(Prompt:="Please Select Range 2", Type:=8)
            range3 = Application.InputBox(Prompt:="Please Select Range 3", Type:=8)
            range4 = Application.InputBox(Prompt:="Please Select Range 4", Type:=8)
            range5 = Application.InputBox(Prompt:="Please Select Range 5", Type:=8)
            range6 = Application.InputBox(Prompt:="Please Select Range 6", Type:=8)
            range7 = Application.InputBox(Prompt:="Please Select Range 7", Type:=8)


            array1 = [range1]
            array2 = [range2]
            array3 = [range3]
            array4 = [range4]
            array5 = [range5]
            array6 = [range6]
            array7 = [range7]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                For b = 1 To UBound(array2)
                    For c = 1 To UBound(array3)
                        For d = 1 To UBound(array4)
                            For e = 1 To UBound(array4)
                                For f = 1 To UBound(array4)
                                    For g = 1 To UBound(array4)
                                        z = z + 1
                                        startrange.Offset(z, 0).Value = array1(a, 1)
                                        startrange.Offset(z, 1).Value = array1(b, 1)
                                        startrange.Offset(z, 2).Value = array1(c, 1)
                                        startrange.Offset(z, 3).Value = array1(d, 1)
                                        startrange.Offset(z, 3).Value = array1(e, 1)
                                        startrange.Offset(z, 3).Value = array1(f, 1)
                                        startrange.Offset(z, 3).Value = array1(g, 1)
                                    Next g
                                Next f
                            Next e
                        Next d
                    Next c
                Next b
            Next a
        Case 8
            range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            range2 = Application.InputBox(Prompt:="Please Select Range 2", Type:=8)
            range3 = Application.InputBox(Prompt:="Please Select Range 3", Type:=8)
            range4 = Application.InputBox(Prompt:="Please Select Range 4", Type:=8)
            range5 = Application.InputBox(Prompt:="Please Select Range 5", Type:=8)
            range6 = Application.InputBox(Prompt:="Please Select Range 6", Type:=8)
            range7 = Application.InputBox(Prompt:="Please Select Range 7", Type:=8)
            range8 = Application.InputBox(Prompt:="Please Select Range 8", Type:=8)


            array1 = [range1]
            array2 = [range2]
            array3 = [range3]
            array4 = [range4]
            array5 = [range5]
            array6 = [range6]
            array7 = [range7]
            array8 = [range8]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                For b = 1 To UBound(array2)
                    For c = 1 To UBound(array3)
                        For d = 1 To UBound(array4)
                            For e = 1 To UBound(array4)
                                For f = 1 To UBound(array4)
                                    For g = 1 To UBound(array4)
                                        For h = 1 To UBound(array4)
                                            z = z + 1
                                            startrange.Offset(z, 0).Value = array1(a, 1)
                                            startrange.Offset(z, 1).Value = array1(b, 1)
                                            startrange.Offset(z, 2).Value = array1(c, 1)
                                            startrange.Offset(z, 3).Value = array1(d, 1)
                                            startrange.Offset(z, 3).Value = array1(e, 1)
                                            startrange.Offset(z, 3).Value = array1(f, 1)
                                            startrange.Offset(z, 3).Value = array1(g, 1)
                                            startrange.Offset(z, 3).Value = array1(h, 1)
                                        Next h
                                    Next g
                                Next f
                            Next e
                        Next d
                    Next c
                Next b
            Next a
        Case 9
                        range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            range2 = Application.InputBox(Prompt:="Please Select Range 2", Type:=8)
            range3 = Application.InputBox(Prompt:="Please Select Range 3", Type:=8)
            range4 = Application.InputBox(Prompt:="Please Select Range 4", Type:=8)
            range5 = Application.InputBox(Prompt:="Please Select Range 5", Type:=8)
            range6 = Application.InputBox(Prompt:="Please Select Range 6", Type:=8)
            range7 = Application.InputBox(Prompt:="Please Select Range 7", Type:=8)
            range8 = Application.InputBox(Prompt:="Please Select Range 8", Type:=8)
            range9 = Application.InputBox(Prompt:="Please Select Range 9", Type:=8)


            array1 = [range1]
            array2 = [range2]
            array3 = [range3]
            array4 = [range4]
            array5 = [range5]
            array6 = [range6]
            array7 = [range7]
            array8 = [range8]
            array9 = [range9]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                For b = 1 To UBound(array2)
                    For c = 1 To UBound(array3)
                        For d = 1 To UBound(array4)
                            For e = 1 To UBound(array4)
                                For f = 1 To UBound(array4)
                                    For g = 1 To UBound(array4)
                                        For h = 1 To UBound(array4)
                                            For i = 1 To UBound(array4)
                                                z = z + 1
                                                startrange.Offset(z, 0).Value = array1(a, 1)
                                                startrange.Offset(z, 1).Value = array1(b, 1)
                                                startrange.Offset(z, 2).Value = array1(c, 1)
                                                startrange.Offset(z, 3).Value = array1(d, 1)
                                                startrange.Offset(z, 3).Value = array1(e, 1)
                                                startrange.Offset(z, 3).Value = array1(f, 1)
                                                startrange.Offset(z, 3).Value = array1(g, 1)
                                                startrange.Offset(z, 3).Value = array1(h, 1)
                                                startrange.Offset(z, 3).Value = array1(i, 1)
                                            Next i
                                        Next h
                                    Next g
                                Next f
                            Next e
                        Next d
                    Next c
                Next b
            Next a


        Case 10
            range1 = Application.InputBox(Prompt:="Please Select Range 1", Type:=8)
            range2 = Application.InputBox(Prompt:="Please Select Range 2", Type:=8)
            range3 = Application.InputBox(Prompt:="Please Select Range 3", Type:=8)
            range4 = Application.InputBox(Prompt:="Please Select Range 4", Type:=8)
            range5 = Application.InputBox(Prompt:="Please Select Range 5", Type:=8)
            range6 = Application.InputBox(Prompt:="Please Select Range 6", Type:=8)
            range7 = Application.InputBox(Prompt:="Please Select Range 7", Type:=8)
            range8 = Application.InputBox(Prompt:="Please Select Range 8", Type:=8)
            range9 = Application.InputBox(Prompt:="Please Select Range 9", Type:=8)
            range10 = Application.InputBox(Prompt:="Please Select Range 10", Type:=8)


            array1 = [range1]
            array2 = [range2]
            array3 = [range3]
            array4 = [range4]
            array5 = [range5]
            array6 = [range6]
            array7 = [range7]
            array8 = [range8]
            array9 = [range9]
            array10 = [range10]


            Set startrange = MatrixSheet.range("C1")
            For a = 1 To UBound(array1)
                For b = 1 To UBound(array2)
                    For c = 1 To UBound(array3)
                        For d = 1 To UBound(array4)
                            For e = 1 To UBound(array4)
                                For f = 1 To UBound(array4)
                                    For g = 1 To UBound(array4)
                                        For h = 1 To UBound(array4)
                                            For i = 1 To UBound(array4)
                                                For j = 1 To UBound(array4)
                                                    z = z + 1
                                                    startrange.Offset(z, 0).Value = array1(a, 1)
                                                    startrange.Offset(z, 1).Value = array1(b, 1)
                                                    startrange.Offset(z, 2).Value = array1(c, 1)
                                                    startrange.Offset(z, 3).Value = array1(d, 1)
                                                    startrange.Offset(z, 3).Value = array1(e, 1)
                                                    startrange.Offset(z, 3).Value = array1(f, 1)
                                                    startrange.Offset(z, 3).Value = array1(g, 1)
                                                    startrange.Offset(z, 3).Value = array1(h, 1)
                                                    startrange.Offset(z, 3).Value = array1(i, 1)
                                                    startrange.Offset(z, 3).Value = array1(j, 1)
                                                Next j
                                            Next i
                                        Next h
                                    Next g
                                Next f
                            Next e
                        Next d
                    Next c
                Next b
            Next a
    End Select
End Sub
 
Last edited:
Upvote 0
Really?

Did you try just adding additional columns of input to the right and pushing the button?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr][tr][td]
4​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Fried rice[/td][td]Fortune Cookie[/td][td]Alan[/td][/tr]
[tr][td]
5​
[/td][td]Egg roll[/td][td]Orange Beef[/td][td]Steamed rice[/td][td]Ice Cream[/td][td]Barb[/td][/tr]
[tr][td]
6​
[/td][td]Wonton soup[/td][td]Pork Lo Mein[/td][td]Rice noodles[/td][td][/td][td]Cain[/td][/tr]
[tr][td]
7​
[/td][td][/td][td]Roast Duck[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td][/td][td]Szechuan Chicken[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Fried rice[/td][td]Fortune Cookie[/td][td]Alan[/td][/tr]
[tr][td]
11​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Fried rice[/td][td]Fortune Cookie[/td][td]Barb[/td][/tr]
[tr][td]
12​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Fried rice[/td][td]Fortune Cookie[/td][td]Cain[/td][/tr]
[tr][td]
13​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Fried rice[/td][td]Ice Cream[/td][td]Alan[/td][/tr]
[tr][td]
14​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Fried rice[/td][td]Ice Cream[/td][td]Barb[/td][/tr]
[tr][td]
15​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Fried rice[/td][td]Ice Cream[/td][td]Cain[/td][/tr]
[tr][td]
16​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Steamed rice[/td][td]Fortune Cookie[/td][td]Alan[/td][/tr]
[tr][td]
17​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Steamed rice[/td][td]Fortune Cookie[/td][td]Barb[/td][/tr]
[tr][td]
18​
[/td][td]Dumplings[/td][td]Kung Pao Shrimp[/td][td]Steamed rice[/td][td]Fortune Cookie[/td][td]Cain[/td][/tr]
[/table]
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
497
rahildhody
R
U
  • Solved
Help: I need to return a list of all cells (will be multiple) containing a specific text string
Replies
8
Views
477
duggie33
D
M
  • Question Question
VBA code failing and miscalculating when running high numbers of lines (200+) but ok when doing it line by line
Replies
3
Views
118
DanteAmor
DanteAmor
A
  • Question Question
[Excel / VBA] How do align some columns exporting to TXT?
Replies
3
Views
374
Joe4
Joe4
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
369
Cquake
C

Forum statistics

Threads
1,226,812
Messages
6,193,118
Members
453,777
Latest member
Miceal Powell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top