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

# 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
• 2
1 Solution

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

Commented:
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

RetiredCommented:
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.