• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

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.
0
bcsmess
Asked:
bcsmess
  • 2
1 Solution
 
GrahamSkanRetiredCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now