VB Difficult Summation/Iteration Question

If I had 100 cells A1 through A100, how do I do a loop to select all possible combinations of 3 of the 100 cells.  And then how do I have 3 variables that sum to 100 so that they also loop through all combinations (integers only).

So z = v1 * c1 + v2 * c2 + v3 * c3 for each combination of c1,c2,c3 in those 100 cells and each combination of v1, v2, v3 for each integer combination of their sum to 100.

Thanks so much.
bcsmessAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
GrahamSkanConnect With a Mentor RetiredCommented:
I can give you the code, but I can't give you the time to run it!

Sub CalculateCombos()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim v1 As Integer
Dim v2 As Integer
Dim v3 As Integer
Dim c1 As Integer
Dim c2 As Integer
Dim c3 As Integer
Dim total As Long
Const maxv = 100

With ActiveWorkbook.ActiveSheet
    For i = 1 To 98
        c1 = .Cells(i, 1)
        For j = i + 1 To 99
            c2 = .Cells(j, 1)
            For k = j + 1 To 100
                c3 = .Cells(k, 1)
                For v1 = 1 To maxv
                    For v2 = 1 To maxv
                        For v3 = 1 To maxv
                            total = v1 * c1 + v2 * c2 + v3 * c3
                        Next v3
                    Next v2
                Next v1
            Next k
        Next j
    Next i
End With
End Sub
0
 
AjithJoseCommented:
This is a problem of permutations, the formula is

nCr=n!/((n-r)!/r!)

In this case: 167000 unique combinations of cells are available

For sample code visit these urls:
http://www.vbexplorer.com/VBExplorer/files/permutation.zip

http://www.codeguru.com/vb/gen/vb_misc/algorithms/article.php/c5607/
http://www.vb-helper.com/howto_permute.html

~ AjithJose
0
 
GrahamSkanRetiredCommented:
AjithJose,

That's just the cells.
The questioner also wants to use a multiplier (maximum unspecified) for each cell, so I guess there are actually
167000* the maximum multiplier value raised to the power of three.
In my code I have assumed that the maximum will be 100, so there would be 167,000,000,000 possibilities.
0
All Courses

From novice to tech pro — start learning today.