Hard time putting loop to code

[iliauk]

Hi All,

I want to create the following algorithm however can't seem to get the loops the right way round and was hoping to get a bit of help.

Hints rather than full solutions would be appreciated as this is more we wanting to learn than to get this to work

Say I have 5 cells. I want the algorithm to do this

Debug.Print:
(Sum 1)
Cell(C)1
C2
C3
C4
C5

(Sum 2)
C1+C2
C1+C3
C1+C4
C1+C5
C2+C3
C2+C4
C2+C5
C3+C4
C3+C5
C4+C5

(Sum 3)
C1+C2+C3
C1+C2+C4
C1+C2+C5
C1+C3+C4
C1+C3+C5
C1+C4+C5
C2+C3+C4
C2+C3+C5
C2+C4+C5
C3+C4+C5

(Sum 4)
C1+C2+C3+C4
C1+C2+C3+C5
C1+C2+C4+C5
C1+C3+C4+C5
C2+C3+C4+C5

(Sum 5)
C1+C2+C3+C4+C5

So in short: The outer-loop will be going from 1 to N products (where N is the last cell e.g. 5 in my example)
Then it starts with
For n = 1 to N

i=1: Ci + C(i+1) + C(i+2) + ... + C(i+N-1)
i=1: Ci + C(i+1) + C(i+2) + ... + C(i+N-1+1)
i=1: Ci + C(i+1) + C(i+2) + ... + C(i+N-1+2)
i=1: Ci + C(i+1) + C(i+2) + ... + C(i+N-1+j)
...
Till (i+N-1+j)=N

Then to increment the product before by one
i=1: Ci + C(i+1) + C(i+2+1) + ... + C(i+N-1+1)

And then repeat the iteration of the the last combo till it reaches N; then iterate the cell before by 1 again, repeat. Once the cell before = N, iterate the cell before that by 1 and then repeat.

Hope this makes sense! Thank you!

 

[Comfy]

This was quite a brain teaser for me too and with some time I could probably clean up the code etc....

But here is what I came up with:

Option Explicit
Public PermCount As Long
Sub SumPermut()


Dim N   As Long
Dim Str As String


PermCount = 1
N = 5


If N > 20 Then MsgBox "There are not enough rows in an Excel workbook to do all the permutaions of " & N & " numbers.  The function will teminate.": Exit Sub


NestedLoop 1, N, Str, "C"


End Sub
Function NestedLoop(iStart As Long, iEnd As Long, InString As String, ColLet As String) As String


Dim i   As Long
Dim Str As String


For i = iStart To iEnd
Str = InString & ColLet & i & "+"
Str = NestedLoop(i + 1, iEnd, Str, ColLet)
Next i


If InString <> "" Then
InString = Left(InString, Len(InString) - 1)
Cells(PermCount, 10).Value = InString
Cells(PermCount, 11).Value = Evaluate(InString)
PermCount = PermCount + 1
End If


End Function

The first revision of this code simply built a string (the same as your example).
I then thought to myself how can I change it so that it sums numbers.
Upon thinking about this I realised I didn't have to change anything we already have the addresses of the cells that we wish to SUM so if I pass this string to the EVALUATE function this should return the cell values as a sum.

With a little work you could change it so that you Pass a Range Object so that N and the Column Letter can be determined automatically.

 

[iliauk]

aha I see! Thanks very much!

I think I was a bit vague in my previous post: what was puzzling me was that I used

For i = iStart To (iEnd)
     sumString = i & "+" & Recursion(i + 1, iEnd, sumString)
Next i
Debug.Print sumString

Rather than

Dim sumString As String
For i = iStart To (iEnd)
    sumString = iString & i
    sumString = Recursion(i + 1, iEnd, sumString)
Next i


Debug.Print iString

Haven't used functions much so I guess was a bit confused what to pass on.

Thanks again for your help! Definitely an interesting problem for me.

 

[Comfy]

Thanks again for your help! Definitely an interesting problem for me.

It was an interesting problem for me too