Macro for combination of numbers that starts with a given sequence

[JOAO12]

Hello everybody,

From A1 to A15 there are numbers ("1" to "15"). In B1, there is number "4". The following code will create combinations of these 15 numbers, in groups of 4. The first combination generated is "1, 2, 3, 4".

Sub Combinations()
Dim rRng As Range, p As Integer
Dim vElements, lRow As Long, vresult As Variant
Set rRng = Range("A1", Range("A1").End(xlDown)) ' The set of numbers
p = Range("B1").Value ' How many are picked
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Call CombinationsNP(vElements, p, vresult, 1, 1)
End Sub
Sub CombinationsNP(vElements As Variant, p As Integer, vresult As Variant, iElement As Integer, iIndex As Integer)
Dim i As Integer
For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        Cells(Application.Rows.Count, 3).End(xlUp).Offset(1, 0).Resize(, p) = vresult
        Cells(Application.Rows.Count, 3).End(xlUp).Select
        
    Else
        Call CombinationsNP(vElements, p, vresult, i + 1, iIndex + 1)
        Cells(Application.Rows.Count, 3).End(xlUp).Select
        
    End If
    
Next i
End Sub

I need to make some changes so that the first combination generated is "1, 3, 5, 9" (and not "1, 2, 3, 4").

Any help will be greatly appreciated!

Regards,

John

 

[JOAO12]

Hello eveybody,

Just an explanation: the first combination generated should be "1, 3, 5, 9". The improved macro should continue creating combinations from this sequence to the end ("12, 13, 14, 15").

Regards,

John

 

[JLGWhiz]

Without changing a whole lot of stuff, here is one approach.

Sub Combinations()
Dim rRng As Range, p As Integer
Dim vElements, lRow As Long, vresult As Variant
Set rRng = Range("A1", Range("A1").End(xlDown)) ' The set of numbers
p = Range("B1").Value ' How many are picked
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Call CombinationsNP(vElements, p, vresult, 1, 1)
End Sub
Sub CombinationsNP(vElements As Variant, p As Integer, vresult As Variant, iElement As Integer, iIndex As Integer)
Dim i As Integer
For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        If vresult(1) >= 1 And vresult(2) >= 3 And vresult(3) >= 5 And vresult(4) >= 9 Then
        Cells(Application.Rows.Count, 3).End(xlUp).Offset(1, 0).Resize(, p) = vresult
        Cells(Application.Rows.Count, 3).End(xlUp).Select
        End If
    Else
        Call CombinationsNP(vElements, p, vresult, i + 1, iIndex + 1)
        Cells(Application.Rows.Count, 3).End(xlUp).Select
        
    End If
    
Next i
End Sub

 

[JLGWhiz]

I don't believe you can permutate 1 To 15 x 4 and have the first number set generated be 1, 3, 5, 9. But as illustrated in the modified code, you can have that be the first set displayed.

 

[JOAO12]

Hello JLGWhiz,

Thank you very much for your help.

Now, the combination starts with the desired sequence. But, unfortunately, there are some sequences missing, like "1, 3, 6, 7" and "1, 3, 6, 8". As the "vresult(4)" is set to be >=9, the numbers "7" and "8" are ignored and, then, the sequences mentioned are not generated.

If you run the "original" macro, you'll see that the sequence "1, 3, 5, 9" occupies row number 94. I'm wondering if there is any way to make this macro starts with row 94.

I really appreciate your help.

Thanks,

John

 

[JLGWhiz]

Sorry about the delayed response, there have been local and network problems.

I couldn't come up with an easy way to count the 94 iterations, because it is actually more than that. But here is an alternative that seems to work. Check it out.

Sub Combinations()
Dim rRng As Range, p As Integer
Dim vElements, lRow As Long, vresult As Variant
Set rRng = Range("A1", Range("A1").End(xlDown)) ' The set of numbers
p = Range("B1").Value ' How many are picked
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Call CombinationsNP(vElements, p, vresult, 1, 1)
End Sub
Sub CombinationsNP(vElements As Variant, p As Integer, vresult As Variant, iElement As Integer, iIndex As Integer)
Dim i As Integer
For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        If Range("C2") = "" And vresult(1) >= 1 And vresult(2) >= 3 And vresult(3) >= 5 And vresult(4) >= 9 Then
            Cells(Application.Rows.Count, 3).End(xlUp).Offset(1, 0).Resize(, p) = vresult
        ElseIf Range("C2") <> "" Then
            Cells(Application.Rows.Count, 3).End(xlUp).Offset(1, 0).Resize(, p) = vresult
            'Cells(Application.Rows.Count, 3).End(xlUp).Select
        End If
    Else
        Call CombinationsNP(vElements, p, vresult, i + 1, iIndex + 1)
        Cells(Application.Rows.Count, 3).End(xlUp).Select
        
    End If
    
Next i
End Sub

 

[JOAO12]

Dear JLGWhiz,

Your code solved the problem.

I am very grateful for your help.

Regards,

John

 

[JLGWhiz]

Dear JLGWhiz,

Your code solved the problem.

I am very grateful for your help.

Regards,

John

Glad you could use it,
Regards, JLG