combinations

Re: combinations

This is something I wrote a little while ago when working with Binomial Coefficients and imposing various output conditions and I must admit I am still working on it however you are welcome to mod it to your hearts content. I have only posted the code that does the actual combinations - see the attachment for full code

Option Explicit


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
On Error GoTo errHandler
For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        'test array vresult for conditions as defined in data entry
        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
        If oddNo = oddNoReq And evenNo = evenNoReq And sumArr >= minSumValue And sumArr <= maxSumValue Then
        lRow = lRow + 1
        Range("C" & lRow).Resize(, p) = vresult
        Range("I" & 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
 
        sumArr = 0
        evenNo = 0
        oddNo = 0
       
Next i


Exit Sub
errHandler:
MsgBox "Error has occured - error no " & Err.Number & " - " & Err.Description
End Sub


Public Function lastRwCt() As Long
Dim o As Long
lastRwCt = Range("A1").CurrentRegion.Count
End Function

Re: combinations

What version of excel are you using?

Re: combinations

Try this version - the numbers in column A must be in ascending order however the code should do that. It has been a while since I have worked with this however as I said you are welcome to mod it to your needs. I checked this version and it will work with 2003.

Re: combinations

Quote from smuzoen;587625

Try this version - the numbers in column A must be in ascending order however the code should do that. It has been a while since I have worked with this however as I said you are welcome to mod it to your needs. I checked this version and it will work with 2003.

.
Hey!. I am working in something similar like the other person, and I tried your code, really good, except I don't really need all possible combinations I just need the ones with the criteria. anything you can do thankssss. newjersey.

Re: combinations

Tnks, smuzoen, I gonna say again, I tried your code (83.kb) because the other one did not work. (I know, my version is 003), I also saw somebody is working on something similar, but not the same. I don't really need all the combinations, definately NOT. First I already have a big array of combinations to omit and in the same manner avoiding the corresponding different. Your sum range works for me, your evens and odds the same, but you don't have the gaps. if you decide to cooperate in my project let me know, at the university here nobody gets my idea.

Re: combinations

smuzoen your code is great, just son adjustment would be perfect. like I don't need all possible combination just the combination that fit the input.

Re: combinations

smuzoen. I tried your code, do you think is possible to add one more definition?. you write a sum range what about if you putting as input VALUES OFF. mean among the sum range there are some sum values that are not need it, at least to be able to turn off 18 sum values. example, minimum value 125 maximum value 155
but turn off the values 137 139 142 etc. and 14 more values. can you please. I am using the excel 2003