# Hard time putting loop to code

#### iliauk

##### Board Regular
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!

Last edited:

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Comfy

##### Well-known Member
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

##### Well-known Member
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
RSTUV
212345
3C1C1C1C1C1
4C1+C2C1+C2C1+C2C1+C2
5C2C1+C2+C3C1+C2+C3C1+C2+C3
6C1+C3C1+C2+C3+C4C1+C2+C3+C4
7C2C1+C2+C4C1+C2+C3+C4+C5
8C2+C3C1+C3C1+C2+C3+C5
9C3C1+C3+C4C1+C2+C4
10C1+C4C1+C2+C4+C5
11C2C1+C2+C5
12C2+C3C1+C3
13C2+C3+C4C1+C3+C4
14C2+C4C1+C3+C4+C5
15C3C1+C3+C5
16C3+C4C1+C4
17C4C1+C4+C5
18C1+C5
19C2
20C2+C3
21C2+C3+C4
22C2+C3+C4+C5
23C2+C3+C5
24C2+C4
25C2+C4+C5
26C2+C5
27C3
28C3+C4
29C3+C4+C5
30C3+C5
31C4
32C4+C5
33C5

</tbody>
Sheet1
Do they look correct?

Excel 2010
EF
3NPermutaions
411
523
637
7415
8531
9663
107127
118255
129511
13101023
14112047
15124095
16138191
171416383
181532767
191665535
2017131071
2118262143
2219524287
23201048575
Sheet1

#### iliauk

##### Board Regular
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]:
Code:
``````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+(i+1)+(i+1)...+(i+(n-1))

So I guess

Code:
``````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

Last edited:

#### Arithos

##### Well-known Member
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

##### Well-known Member
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 could post some snippets of the function that I wrote??

#### iliauk

##### Board Regular
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

##### Well-known Member
Ok, here's the start of the function and it required objects:

Code:
``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

Code:
``````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.

Last edited:

#### Comfy

##### Well-known Member
And here's the Sub that calls the function:

Code:
``````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

##### Board Regular
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:

Code:
``````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:

Code:
``````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

Replies
9
Views
259
Replies
1
Views
143
Replies
6
Views
229
Replies
14
Views
284
Replies
17
Views
559

1,191,123
Messages
5,984,774
Members
439,910
Latest member
Flyingjoblo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back