Results 1 to 5 of 5

Thread: Arrays

  1. #1
    cfe5001
    Guest

    Arrays

    I have a column with a different integer value in each of 21 rows. How can I loop through the values until I get 12 of the values to sum to a specific amount?
    [img]/w3timages/icons/hairy.gif[/img]

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays

    We need a little more information:

    1- Does it have to be 12 consecutive values, or should it be any combination of the 21 cells?

    2- Can a particular cell be used more that once?

    3- What result do you want and how do you want it. Surly you don't want the sum since you know that going in. Do you want a list of the indexes of the cells used? Do you want a list of the values used? In an array?

    I assume you are talking about doing this in VBA.
    Legare Coleman

  3. #3
    cfe5001
    Guest

    Re: Arrays

    1- No, it should be any combination of the 21 cells.
    2- No, a cell can only be used once.
    3- Prefered result would be a list of the values.

    Yes I was prefering to use VBA.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Arrays

    I am not going to write the VBA for you but I can give a few pointers.

    I would suggest you set the first 12 cells in a column to the right of the list to equal the corresponding list number. Put a total of this column at the bottom and test if it = the target.

    Then you need to work out a process for stepping through the list moving the spaces up into the list. Clearly the first step will be to make the 12th cell blank and the 13th cell = its corresponding figure. Then you make the 11 cell blank and make the 12 cell = its corresponding figure.

    You would probably have a series of nested loops to do this but if you were very clever and wanted to minimise the size of the code you would use a recursive sub.

    Back in the old days we used to do things like this for fun - see how much you can do with how little programming!
    David Grugeon
    Brisbane Australia

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays

    Ok, the code below will find the combination of 12 cells in the range A1:A21 that adds up to the sum passed as a parameter to the subroutine. First, a couple of warnings. The procedure is written to expect to find an exact match on the sum and therefore is only guaranted to work on integer values. If the cells can contain real numbers, the procedure will have to be modified to deal with the rounging errors. Second, be patient. This procedure can run for a long time. To find the sum where the values were in the last 12 cells took over 5 minutes on the 400mz system I tested it on.

    <pre>Public Sub Find12(dReqTot As Double)
    Dim I1 As Integer, I2 As Integer, I3 As Integer, I4 As Integer, I5 As Integer, I6 As Integer
    Dim I7 As Integer, I8 As Integer, I9 As Integer, I10 As Integer, I11 As Integer, I12 As Integer
    Dim dSum As Double, dList(1 To 12) As Double
    Dim strWk As String
    For I1 = 1 To 10
    For I2 = I1 + 1 To 11
    For I3 = I2 + 1 To 12
    For I4 = I3 + 1 To 13
    For I5 = I4 + 1 To 14
    For I6 = I5 + 1 To 15
    For I7 = I6 + 1 To 16
    For I8 = I7 + 1 To 17
    For I9 = I8 + 1 To 18
    For I10 = I9 + 1 To 19
    For I11 = I10 + 1 To 20
    For I12 = I11 + 1 To 21
    dSum = Cells(I1, 1) + Cells(I2, 1) + Cells(I3, 1)
    dSum = dSum + Cells(I4, 1) + Cells(I5, 1) + Cells(I6, 1)
    dSum = dSum + Cells(I7, 1) + Cells(I8, 1) + Cells(I9, 1)
    dSum = dSum + Cells(I10, 1) + Cells(I11, 1) + Cells(I12, 1)
    If dSum = dReqTot Then Exit For
    Next I12
    If dSum = dReqTot Then Exit For
    Next I11
    If dSum = dReqTot Then Exit For
    Next I10
    If dSum = dReqTot Then Exit For
    Next I9
    If dSum = dReqTot Then Exit For
    Next I8
    If dSum = dReqTot Then Exit For
    Next I7
    If dSum = dReqTot Then Exit For
    Next I6
    If dSum = dReqTot Then Exit For
    Next I5
    If dSum = dReqTot Then Exit For
    Next I4
    If dSum = dReqTot Then Exit For
    Next I3
    If dSum = dReqTot Then Exit For
    Next I2
    If dSum = dReqTot Then Exit For
    Next I1
    If dSum = dReqTot Then
    strWk = Cells(I1, 1) & "+" & Cells(I2, 1) & "+" & Cells(I3, 1) & "+"
    strWk = strWk & Cells(I4, 1) & "+" & Cells(I5, 1) & "+" & Cells(I6, 1) & "+"
    strWk = strWk & Cells(I7, 1) & "+" & Cells(I8, 1) & "+" & Cells(I9, 1) & "+"
    strWk = strWk & Cells(I10, 1) & "+" & Cells(I11, 1) & "+" & Cells(I12, 1) & "=" & dReqTot
    MsgBox strWk
    Else
    MsgBox "No combination adds up to " & dReqTot
    End If
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •