VB Difficult Summation/Iteration Question

Posted on 2004-10-21
Last Modified: 2006-11-17
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.
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
    LVL 4

    Expert Comment

    This is a problem of permutations, the formula is


    In this case: 167000 unique combinations of cells are available

    For sample code visit these urls:

    ~ AjithJose
    LVL 76

    Expert Comment


    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    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…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now