Solved

# VB Difficult Summation/Iteration Question

Posted on 2004-10-21
187 Views
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
Question by:bcsmess

LVL 76

Accepted Solution

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

LVL 4

Expert Comment

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

LVL 76

Expert Comment

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

## Featured Post

### Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…