Generalizing Nested Loops

[syntaxed]

Hi there,

Apologies if a similar query has already been raised and answered before - I tried searching but couldn't find a solution.

I have code that looks like this:

'for 1 event(s) (need to generalize)
    For i = 0 To LossSet - 1
        Cells(RowCount, "C").Value = LossArray(i)
        RowCount = RowCount + 1
    Next
 
    'for 2 event(s) (need to generalize)
    For i = 0 To LossSet - 1
    For j = 0 To LossSet - 1
        Cells(RowCount, "C").Value = LossArray(i)
        Cells(RowCount, "D").Value = LossArray(j)
        RowCount = RowCount + 1
    Next
    Next
 
    'for 3 event(s) (need to generalize)
    For i = 0 To LossSet - 1
    For j = 0 To LossSet - 1
    For k = 0 To LossSet - 1
        Cells(RowCount, "C").Value = LossArray(i)
        Cells(RowCount, "D").Value = LossArray(j)
        Cells(RowCount, "E").Value = LossArray(k)
        RowCount = RowCount + 1
    Next
    Next
    Next
 
    'for 4 event(s) (need to generalize)
    For i = 0 To LossSet - 1
    For j = 0 To LossSet - 1
    For k = 0 To LossSet - 1
    For l = 0 To LossSet - 1
        Cells(RowCount, "C").Value = LossArray(i)
        Cells(RowCount, "D").Value = LossArray(j)
        Cells(RowCount, "E").Value = LossArray(k)
        Cells(RowCount, "F").Value = LossArray(l)
        RowCount = RowCount + 1
    Next
    Next
    Next
    Next

I am looking to expand this for 5, 6, 7, ..... basically any level of nestings, thereby saving me from a lot of copy-pastings. Any help would be most appreciated.

 

[Simon Lloyd]

Please supply the whole section of code then we can help further, right now you really don't need i, j, k,....etc one will suffice.

 

[GTO]

Are you saying that LossArray is dynamically filled and may have less or more 'columns' in it?

 

[Dryver14]

does i = loss set - 1
and j = Loss set - 1

etc

lead to the same value?

 

[syntaxed]

Thanks all for the feedback.

I think if I fully explain the problem, all queries will be satisfied.
I have a worksheet in which column A contains possible "Loss Values", column B contains possible "Number of Loss Events".

Example:
Col A | Col B
500000 | 1
600000 | 2
1000000 | 3
5500000 | 4

I need to construct all possible combinations for Loss Values in column A for all possible Number of Loss Events in Col B. My code looks like this:

Sub Combo()    
    Dim LossSet As Long, Losses As Long
    LossSet& = Range("A" & Rows.Count).End(xlUp).Row
    Dim LossArray()
    ReDim LossArray(LossSet)
 
    For Losses = 0 To LossSet - 1
        LossArray(Losses) = Cells(Losses + 1, 1).Value
    Next
 
    RowCount = 1
 
    'for 1 event(s) (need to generalize)
    For i = 0 To LossSet - 1
        Cells(RowCount, "C").Value = LossArray(i)
        RowCount = RowCount + 1
    Next
 
    'for 2 event(s) (need to generalize)
    For i = 0 To LossSet - 1
    For j = 0 To LossSet - 1
        Cells(RowCount, "C").Value = LossArray(i)
        Cells(RowCount, "D").Value = LossArray(j)
        RowCount = RowCount + 1
    Next
    Next
 
    'for 3 event(s) (need to generalize)
    For i = 0 To LossSet - 1
    For j = 0 To LossSet - 1
    For k = 0 To LossSet - 1
        Cells(RowCount, "C").Value = LossArray(i)
        Cells(RowCount, "D").Value = LossArray(j)
        Cells(RowCount, "E").Value = LossArray(k)
        RowCount = RowCount + 1
    Next
    Next
    Next
 
    'for 4 event(s) (need to generalize)
    For i = 0 To LossSet - 1
    For j = 0 To LossSet - 1
    For k = 0 To LossSet - 1
    For l = 0 To LossSet - 1
        Cells(RowCount, "C").Value = LossArray(i)
        Cells(RowCount, "D").Value = LossArray(j)
        Cells(RowCount, "E").Value = LossArray(k)
        Cells(RowCount, "F").Value = LossArray(l)
        RowCount = RowCount + 1
    Next
    Next
    Next
    Next
End Sub

When I run this, it gives me a table of all possible combinations (in columns C:F), something like this...

Col A Col B Col C
500000
600000
1000000
5500000
500000 500000
500000 600000
500000 1000000
500000 5500000
600000 500000
600000 600000
600000 1000000
600000 5500000
1000000 500000
1000000 600000
1000000 1000000
1000000 5500000
5500000 500000
5500000 600000
5500000 1000000
5500000 5500000
500000 500000 500000
500000 500000 600000
500000 500000 1000000
500000 500000 5500000

..... and so on until all possible combinations are exhausted. Right now the code can only handle combinations for upto 4 event. I want to extend this such that all possible combinations are calculated for as many events as I wish which I will have entered in column B. The code will read column B and for each number of events stated there, generate all combinations of loss values (which it reads from A) in columns C onwards.

 

[syntaxed]

Sorry, typo up there. The sequence

"Col A Col B Col C
500000
600000
1000000
5500000
..."

should read:

"Col C Col D Col E
500000
600000
1000000
5500000
..."

 

[GTO]

Sorry, just to be clear, would this portray before and after?

Excel Workbook
	A	B	C	D	E	F
1	Before
2	500	1
3	550	2
4	600	3
5	1200	5
6	1300	2
7	1400	1
8
9	After
10	500
11	550	550
12	600	600	600
13	1200	1200	1200	1200	1200	1200
14	1300	1300
15	1400
Sheet2

Excel 2003

 

[syntaxed]

Before:

http://tinypic.com/r/qwef/7

After:

http://tinypic.com/r/2w30vug/7

It is essentially a problem of combinations with replacement, where the inputs are in columns A and B and outputs are displayed in columns C onwards/

 

[GTO]

Sorry, the site the links point to is blocked where I am. With the exception that the output should be in Col C and thereafter, is my post accurate?

 

[GTO]

Well, I'm about done for today, so here's a stab.

Option Explicit
    
Sub exa()
Dim rngLossVal As Range, Cell As Range
Dim lMaxCols As Long, x As Long, y As Long
Dim aryOutput() As Variant
    
    '// Assumes a header row, alter to suit                                 //
    Set rngLossVal = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))
    '// Find the max number of columns we'll need, to size the second dimension //
    lMaxCols = Application.Max(rngLossVal.Offset(, 1).Value)
    
    '// Size our output array.  As we used a Variant array, un-filled elements  //
    '// will just be empty, so we can just plunk the finished array wherever.   //
    ReDim aryOutput(1 To rngLossVal.Rows.Count, 1 To lMaxCols)
    
    For Each Cell In rngLossVal
        x = x + 1
        For y = 0 To Cell.Offset(, 1).Value - 1
            aryOutput(x, y + 1) = Cell.Value
        Next
    Next
    
    Range("C2").Resize(UBound(aryOutput, 1), UBound(aryOutput, 2)).Value = aryOutput
End Sub