montecarlo2012
Well-known Member
- Joined
- Jan 26, 2011
- Messages
- 984
- Office Version
- 2010
- Platform
- Windows
Hello, I got a code to generate combinations (I got from here mrexcel), but I don't know where to insert a condition.
this code drop down a list of combinations on C:H
I would like to add a condition, IF F minus E = B2 then
show the combination, I am trying to see just the combinations I want not all of them
and on B2 I want to input the different, if I say 3 for example all the combination
that F - E = 3 will be the only combinations show up.
this code drop down a list of combinations on C:H
I would like to add a condition, IF F minus E = B2 then
show the combination, I am trying to see just the combinations I want not all of them
and on B2 I want to input the different, if I say 3 for example all the combination
that F - E = 3 will be the only combinations show up.
Code:
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