montecarlo2012
Well-known Member
- Joined
- Jan 26, 2011
- Messages
- 986
- Office Version
- 2010
- Platform
- Windows
Hello folks
I have been trying to work around this code, I need some hands on here, Please.
I am trying to generate all possible combinations and permutations possible from the set of numbers from 0 to 9 taken four at a time BUT display ONLY the one’s that match the conditions.
The condition that I tried is to have control on odd and even numbers per row and the sum.
The odd and even in this case is only five possible outcomes :
And the total sum will be from 0 to 36, the maximum permutation is 9-9-9-9 so of course 36 is the maximum sum
Also I would like to avoid this two lines
Set rRng = Range("A1", Range("A1").End(xlDown))
rRng.Select: p = 4
because I already know the set of number, and is always 4, but I don’t know how to do it.
The display could start at ("D2")
Thank you.
Thank you for taking time to read this.
I have been trying to work around this code, I need some hands on here, Please.
I am trying to generate all possible combinations and permutations possible from the set of numbers from 0 to 9 taken four at a time BUT display ONLY the one’s that match the conditions.
The condition that I tried is to have control on odd and even numbers per row and the sum.
The odd and even in this case is only five possible outcomes :
| odds | even |
| 0 | 4 |
| 1 | 3 |
| 2 | 2 |
| 3 | 1 |
| 4 | 0 |
Also I would like to avoid this two lines
Set rRng = Range("A1", Range("A1").End(xlDown))
rRng.Select: p = 4
because I already know the set of number, and is always 4, but I don’t know how to do it.
The display could start at ("D2")
Thank you.
VBA Code:
Option Explicit
Public sumArr As Long, oddNo As Long, evenNo As Long, oddNoReq As Long, lastRow As Long, _
evenNoReq As Long, minSumValue As Long, maxSumValue As Long, lRow As Long, testRow As Long, minMaxRn As Long
Sub Combinations()
oddNoReq = Range("B27"): evenNoReq = Range("B26")
minSumValue = Range("B29"): maxSumValue = Range("B30")
Dim rRng As Range, p As Integer
Dim vElements, vresult As Variant
lRow = 1
testRow = 1
Set rRng = Range("A1", Range("A1").End(xlDown))
rRng.Select: p = 4
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p): Columns("C").Resize(, p + 12).Clear
Call CombinationsNP(vElements, p, vresult, lRow, 1, 1)
End Sub
Sub CombinationsNP(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Integer, k As Integer
For i = iElement To UBound(vElements)
vresult(iIndex) = vElements(i)
If iIndex = p Then
For k = LBound(vresult) To UBound(vresult)
If vresult(k) Mod 2 <> 0 Then oddNo = oddNo + 1
If vresult(k) Mod 2 = 0 Then evenNo = evenNo + 1
sumArr = sumArr + vresult(k)
Next k
If oddNo = oddNoReq And evenNo = evenNoReq _
And sumArr >= minSumValue _
And sumArr <= maxSumValue Then
lRow = lRow + 1
Range("S" & lRow) = sumArr
End If
testRow = testRow + 1
Range("k" & testRow).Resize(, p) = vresult
End If
If iIndex <> p Then
Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
End If
Next i
End Sub
Last edited by a moderator:
