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]

Hmmmm, how to hint at how to achieve this....

Create a function that calls itself 1 To N times.

The last line of this function is where you will place your Debug.Print

 

[Comfy]

Just so that I know what I have is what you are after here are the results of running my code for each value of N:

Excel 2010

	R	S	T	U	V
C1	C1	C1	C1	C1
C1+C2	C1+C2	C1+C2	C1+C2
C2	C1+C2+C3	C1+C2+C3	C1+C2+C3
C1+C3	C1+C2+C3+C4	C1+C2+C3+C4
C2	C1+C2+C4	C1+C2+C3+C4+C5
C2+C3	C1+C3	C1+C2+C3+C5
C3	C1+C3+C4	C1+C2+C4
C1+C4	C1+C2+C4+C5
C2	C1+C2+C5
C2+C3	C1+C3
C2+C3+C4	C1+C3+C4
C2+C4	C1+C3+C4+C5
C3	C1+C3+C5
C3+C4	C1+C4
C4	C1+C4+C5
C1+C5
C2
C2+C3
C2+C3+C4
C2+C3+C4+C5
C2+C3+C5
C2+C4
C2+C4+C5
C2+C5
C3
C3+C4
C3+C4+C5
C3+C5
C4
C4+C5
C5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Do they look correct?

Excel 2010
	E	F
3	N	Permutaions
4	1	1
5	2	3
6	3	7
7	4	15
8	5	31
9	6	63
10	7	127
11	8	255
12	9	511
13	10	1023
14	11	2047
15	12	4095
16	13	8191
17	14	16383
18	15	32767
19	16	65535
20	17	131071
21	18	262143
22	19	524287
23	20	1048575
Sheet1

 

[iliauk]

Hi Comfy, thanks very for much for your help.

Your output indeed looks correct and your column V is the final output I am after. So your suggestion is a recursive function?

I will try that. My initial thoughts were something like this but it quickly escalates in terms of scenarios.

[step1]:

for i = 1 to (N-n)+1
...[step2]    
next i

So if I want to replicate my sum 3 list it will go through i=1 to 5-3+1 -> i=3

[step2]:
I want to start with combo:
i+(i+1)+(i+1)...+(i+(n-1))

So I guess

startcombo is null
for j=0 to (n-1)
startcombo = startcombo + (i+j)
k = k +1
next j

Now I want to iterate the last additon to the startcombo by 1 until it hits N

So I guess:

k=0
Do until (i+j+k)=N+1<n+1

for j=0 to (n-1)
startcombo = start + (i+j+k)
next j
Loop

So in my original example I will get:

C1+C2+C3
C1+C2+C4

Now I want to send a new start combo of:
C1+C3+C4
and repeat the iteration.</n+1

 

[Arithos]

You want an algorithm that does permutation without repetition?

In other words the =FACT(5) formula in excel? only Show your calculations in Excel as the possibilities C(i) ?

EDIT: Just saw your post you can disregard the above.

 

[Comfy]

Firstly I just wrote a very simple Sub with the 5 Loops (N) hard coded.

But then I got to thinking that your N = 5 was just an example and what if you wanted N to be 6 or 7 etc.

So, how do you write a program that increases the number of loops based on a variable? That's why I decide a function that called itself was the answer.

Think of this function as a Loop.

When called from a Sub it's essentially like looping once but if the function calls itself it could be any number of loops.

I'm not really sure how hints would work to help you get to an answer on your own, I'm a terrible teacher.

I could post some snippets of the function that I wrote??

 

[iliauk]

Comfy that would be very useful or if too much hassle the entire code and I will try to control myself :D

BTW, you are correct that N=5 was an example and something like N=10 would be interesting for me too.

 

[Comfy]

Ok, here's the start of the function and it required objects:

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

I should explain the objects though but apologies if my explanation makes no sense.

InString - When I first wrote this I did string concatenation so that I could verify my output against your example. So this function creates part of a string and then passes that output string to itself as an input string so that additional text can be added on the end.

ColLet - Is used in the String concatenation so that the function knows which column to apply the SUMS.

iStart and iEnd -

Imagine this for N = 1 to 5

Sub test()
Dim i As Long, j As Long, k As Long, l As Long, m As Long
Dim N As Long

N = 5
For i = 1 To N
    For j = i + 1 To N
        For k = j + 1 To N
            'repeated for object l and m
        Next k
    Next j
Next i
End Sub

We need 5 objects (i,j,k,l,m) to hold numbers that will allow us to Loop 5 times.

It's these 5 (or N) objects that can be replaced by one function that calls itself.

At each new loop the starting number increments by 1 -- it's this increment that is represented in my Function by iStart.

iEnd will always be equal to N.

 

[Comfy]

And here's the Sub that calls the function:

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

 

[iliauk]

Hi Comfy, finally got some time to give this a go.

In terms of hard-coding the loop for a value of N; I think this works:

N = 5
For i = 1 To N
    Sumstring = i
    Debug.Print Sumstring
    For j = (i + 1) To N
        Sumstring = i & j
        Debug.Print Sumstring
        For k = (j + 1) To N
            Sumstring = i & j & k
            Debug.Print Sumstring
            For l = (k + 1) To N
                Sumstring = i & j & k & l
                Debug.Print Sumstring
                For m = (l + 1) To N
                    Sumstring = i & j & k & l & m
                    Debug.Print Sumstring
                Next m
            Next l
        Next k
    Next j
Next i

So I tried to generalise that in terms of a function however, can't quite get it to work:

Option Explicit
Public i, iStart, iEnd, N As Long
Public sumString As String
Sub CallRecursion()
N = 5
Recursion 1, N, sumString
End Sub


Function Recursion(ByVal iStart As Long, ByVal iEnd As Long, ByVal sumString As String) As String
For i = iStart To (iEnd)
     sumString = i & "+" & Recursion(i + 1, iEnd, sumString)
Next i
Debug.Print sumString
End Function

Would appreciate to know which way I am going wrong! Thanks