VBA - Dynamic Nested Loops

[stikpet]

Dear madam/sir,
I'm trying to loop over all possible combinations when choosing c items out of a possible n. This is easily done with nested loops, as my example shown below. However, the number of for-loops needed depends on the input parameter of c (the number chosen).
I'm scratching my head but can't figure a way out anymore on how to set 'dynamically' (not sure if thats the proper term) the number of loops.

Here's my example code:

Option Explicit

Sub example()
Dim c, n, r, i, j, k, l As Integer

n = 8
c = 4

r = 1

For i = 1 To n - c + 1
    For j = i + 1 To n - c + 2
        For k = j + 1 To n - c + 3
            For l = k + 1 To n - c + 4
                Cells(r, 1) = i
                Cells(r, 2) = j
                Cells(r, 3) = k
                Cells(r, 4) = l
                r = r + 1
            Next l
        Next k
    Next j
Next i


End Sub

Any suggestions would be highly appreciated.

 

[StephenCrump]

Welcome to the Forum!

Here's one way you could do this:

Sub Test()
    
    Dim lResults() As Long, n As Long, r As Long
    
    n = 8: r = 4
    
    lResults = GetCombinations(n, r)
    Range("A1").Resize(UBound(lResults), UBound(lResults, 2)).Value = lResults
    
End Sub
Function GetCombinations(lNumber As Long, lNoChosen As Long) As Long()

    Dim lOutput() As Long, lCombinations As Long
    Dim i As Long, j As Long, k As Long
    
    lCombinations = WorksheetFunction.Combin(lNumber, lNoChosen)
    ReDim lOutput(1 To lCombinations, 1 To lNoChosen)
    
    For i = 1 To lNoChosen
        lOutput(1, i) = i
    Next i
    
    For i = 2 To lCombinations
        For j = 1 To lNoChosen
            lOutput(i, j) = lOutput(i - 1, j)
        Next j
        For j = lNoChosen To 1 Step -1
            lOutput(i, j) = lOutput(i, j) + 1
            If lOutput(i, j) <= lNumber - (lNoChosen - j) Then Exit For
        Next j
        For k = j + 1 To lNoChosen
            lOutput(i, k) = lOutput(i, k - 1) + 1
        Next k
    Next i
    
    GetCombinations = lOutput
    
End Function

 

[stikpet]

Thanks a lot. It worked perfectly.

p.s. am actually not new but forgot my account details from many years ago. I once even won the Mr. Excel competition :D.

 

[StephenCrump]

Ahh! In that case, welcome back to the Forum!

I'm glad the function worked for you.