How do I shorten this piece of code to print every combination possible (given certain parameters)

Question

I would like to list all combinations of 9 different variables.

Each variable is stored in 9 different columns.

Each variable has different upper limits.

Is there a simpler way to write this code?

Each variable needs to be an even number.

Any "Rest___" variable goes from 8-20 (e.g. 8,10,...18,20)

Any "Work___" variable goes from 8-12 (e.g. 8,10,12)

Dim i As Long
Dim j As Long
Dim Rest1 As Integer
Dim Rest2 As Integer
Dim Rest3 As Integer
Dim Rest4 As Integer

Dim Work1 As Integer
Dim Work2 As Integer
Dim Work3 As Integer
Dim Work4 As Integer
Dim Work5 As Integer

Dim TableRange As Range
'I know the range should have at least 583,443 rows 583,443
Set TableRange = Range("b3:t1000")

i=1
j=1

For Rest1 = 8 To 20 Step 2
    For Rest2 = 8 To 20 Step 2
        For Rest3 = 8 To 20 Step 2
            For Rest4 = 8 To 20 Step 2
                    For Work1 = 8 To 12 Step 2
                        For Work2 = 8 To 12 Step 2
                            For Work3 = 8 To 12 Step 2
                                For Work4 = 8 To 12 Step 2
                                    For Work5 = 8 To 12 Step 2
                                        TableRange(i, j) = Rest1
                                        j = j + 1
                                        TableRange(i, j) = Rest2
                                        j = j + 1
                                        TableRange(i, j) = Rest3
                                        j = j + 1
                                        TableRange(i, j) = Rest4
                                        j = j + 1
                                        TableRange(i, j) = Work1
                                        j = j + 1
                                        TableRange(i, j) = Work2
                                        j = j + 1
                                        TableRange(i, j) = Work3
                                        j = j + 1
                                        TableRange(i, j) = Work4
                                        j = j + 1
                                        TableRange(i, j) = Work5
                                        j = 1
                                        i = i + 1
                                    Next Work5
                                Next Work4
                            Next Work3
                        Next Work2
                    Next Work1
            Next Rest4
        Next Rest3
    Next Rest2
Next Rest1

As my code is, Excel throws a "Run-Time Error '6' Overflow"

I've included a picture of what the output should look like: SampleOutput

@JvdV - Would using "Long" make it more efficient? I thought keeping it as Integer would use less memory (especially since my variables only go from 8 to 20 at the maximum) — newtovba, Oct 11 '19 at 15:33
@Cyril - I have 9 variables so I'm trying to get all the different combinations (e.g. Rest 1 can be from 8 to 20, Rest 2 can be from 8 to 20...Work 1 can be from 8 to 12, Work 2 can be from 8 to 12). — newtovba, Oct 11 '19 at 15:34
@newtovba - see [this question](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) on why to use long. — BigBen, Oct 11 '19 at 15:48
You could certainly write it more concisely by using mixed radix https://en.wikipedia.org/wiki/Mixed_radix but it's more difficult conceptually so I don't know whether you would say it was simpler. Basically you just have one big loop instead of 9 nested ones. — Tom Sharpe, Oct 11 '19 at 15:52
@BigBen Gotcha very helpful. Converted them to long - still running into the overflow error (I think because it's essentially having to loop through 500k+ runs) — newtovba, Oct 11 '19 at 15:56
@TomSharpe Oh interesting proposition! Didn't know about this. I'm ok with shorter code despite increased complexity in understanding the code — newtovba, Oct 11 '19 at 15:57
Note - should be faster to build an array with this and then write to the sheet in one go. — BigBen, Oct 11 '19 at 16:05

Tom Sharpe · Accepted Answer · 2019-10-28T14:09:39.057

This is my implementation of mixed base. The output looks like yours apart from the headers - these need to be added.

So imagine a counter which goes like this - each digit resets to zero when it reaches the number of levels in each of your variables:

and so on.

This corresponds to the first rows of your table

8  8  8  8  8  8  8  8  8
8  8  8  8  8  8  8  8 10
8  8  8  8  8  8  8  8 12
8  8  8  8  8  8  8 10  8
8  8  8  8  8  8  8 10 10

Option Explicit

Sub Combinations()

'Set up arrays to define number of levels in each variable and to hold counter.

Dim nValues, startValues, countArray As Variant
nValues = Array(3, 3, 3, 3, 3, 7, 7, 7, 7)
countArray = Array(0, 0, 0, 0, 0, 0, 0, 0, 0)

'Define constants
Const startValue = 8, increment = 2, nCols = 9

'Define variables
Dim row As Long
Dim column, carry As Integer

'Work out number of rows and define an array to hold results

Dim nRows As Variant
nRows = WorksheetFunction.Product(nValues)

Dim holdingArray() As Integer
ReDim holdingArray(nRows, nCols)

'Loop over rows

For row = 0 To nRows - 1
    carry = 0

' Loop over columns

    For column = 0 To nCols - 1
        countArray(column) = countArray(column) + carry

        'Check if a 'carry' is needed

        If countArray(column) = nValues(column) Then
            carry = 1
            countArray(column) = 0
        Else
            carry = 0
        End If

        'Store results (reverse order of columns)

        holdingArray(row, nCols - 1 - column) = startValue + countArray(column) * increment
    Next column

'Increment counter

countArray(0) = countArray(0) + 1
Next row

'Transfer array to sheet

Range(Cells(1, 1), Cells(nRows, nCols)) = holdingArray


End Sub

EDIT

Whilst the original answer does give the correct result, it works out better if the arrays start at 1 (so option base 1). Also I made the rookie error of thinking that you can define several variables of similar type in a single Dim statement without repeating the type.

Version 2:

Option Explicit
Option Base 1

Sub Combinations()

'Define constants
Const startValue = 8, increment = 2

'Define variables
Dim row As Long, nRows As Long
Dim column As Integer, carry As Integer, nCols As Integer

'Set up arrays to define number of levels in each variable and to hold counter.

Dim nValues As Variant, countArray As Variant
nValues = Array(3, 3, 3, 3, 3, 7, 7, 7, 7)

nCols = UBound(nValues)
ReDim countArray(nCols)

Debug.Print ("ubound=" & UBound(nValues))

'Work out number of rows and define an array to hold results

nRows = WorksheetFunction.Product(nValues)

Debug.Print ("nrows=" & nRows)

Dim holdingArray() As Integer
ReDim holdingArray(nRows, nCols)

'Loop over rows

For row = 1 To nRows
    carry = 0

' Loop over columns

    For column = 1 To nCols
        countArray(column) = countArray(column) + carry

        'Check if a 'carry' is needed

        If countArray(column) = nValues(column) Then
            carry = 1
            countArray(column) = 0
        Else
            carry = 0
        End If

        'Store results (reverse order of columns)

        holdingArray(row, nCols + 1 - column) = startValue + countArray(column) * increment
    Next column

'Increment counter

countArray(1) = countArray(1) + 1
Next row

'Transfer array to sheet

Range(Cells(1, 1), Cells(nRows, nCols)) = holdingArray


End Sub

This is brilliant. I had attempted a sort of mixed base using a column counter and decrementing the column counter to signal when it should reset, but I like the code simplicity of this. Much appreciated! — newtovba, Oct 12 '19 at 06:17
I tried adding another variable I needed to count (only needs 8,10) but when I tried changing: nValues = Array(2, 3, 3, 3, 3, 3, 7, 7, 7, 7) countArray = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0) Const startValue = 8, increment = 2, nCols = 10 I run into a run-time error '1004' when trying to print the array. What am I doing wrong? — newtovba, Oct 12 '19 at 06:42
Not doing anything wrong as such, but if you work out the number of rows it's now 1166886 which is more than the maximum number of rows allowed in a sheet! — Tom Sharpe, Oct 12 '19 at 08:27

How do I shorten this piece of code to print every combination possible (given certain parameters)

1 Answers1