Triple Loop in VBA

Question

I am trying to create a triple loop in excel that will read from a table and populate three columns with increasing values that are dependent on the values in a table. So the values in column one and two will repeat until column 3 has finished and then two will increase and 1 will repeat until it is finished, and so on. It should ultimately give me values that look something like this. (where the hyphens are column divides.

1-1-1, 1-1-2, 1-1-3, 1-1-4, 1-2-1, 1-2-2, 1-2-3, 1-2-4, 1-3-1, 1-3-2, 1-3-3, 1-3-4, 2-1-1, 2-1-2, etc...

Here is the code that I currently have, but it is not repeating the values in the first two columns while column 3 counts, and it is stopping when column 1 finishes its first round of counting. It needs to start again as column 2 increases itself to the next value.

Thanks for any help you can provide!

Dim i As Integer, iPos As Integer, c As Integer, iRow As Integer, d As Integer, iShl As Integer

iPos = Worksheets("Sheet1").Cells(3, "J").Value
iShl = Worksheets("Sheet1").Cells(3, "I").Value
iRow = Worksheets("Sheet1").Cells(3, "H").Value
For i = 1 To iPos
 Cells(i, 3).Value = i
    For d = 1 To iShl
        Cells(i, 2).Value = d
            For c = 1 To iRow
                Cells(c, 1).Value = c
            Next c
    Next d
Next i

You will need to set the `Cells(...).Value` in the innermost loop. Also, for your i loop, you will have to count to iPos times iShl times iRow. — MP24, Jun 18 '14 at 14:47
@MP24 I am not sure what you mean in the first statement, but for the second part I added this. The problem is it now only counts all the way to the total of the multiple rather than recycling after it hits its limit. I am assuming this has something to do with the first part of your statement which I admittedly am not sure I am qualified to do. Could you help by editing the first part? — user3749080, Jun 18 '14 at 14:56
What you are trying to generate is called **permutations**. You can find lots of code samples over the web. Some perhaps useful old Stack Overflow question is http://stackoverflow.com/questions/2710713/algorithm-to-generate-all-possible-permutations-of-a-list — xmojmr, Jun 19 '14 at 05:22

MP24 · Answer 1 · 2014-06-18T15:16:44.590

First of all, you will need to write out all values in the innermost loop.

Dim i As Integer, iPos As Integer, c As Integer, iRow As Integer, d As Integer, iShl As Integer

iPos = Worksheets("Sheet1").Cells(3, "J").Value
iShl = Worksheets("Sheet1").Cells(3, "I").Value
iRow = Worksheets("Sheet1").Cells(3, "H").Value
For i = 1 To iPos
    For d = 1 To iShl
        For c = 1 To iRow
            Cells(i, 3).Value = i
            Cells(i, 2).Value = d
            Cells(i, 1).Value = c
        Next c
    Next d
Next i

However, this will not print properly. You can achieve this by just adding a counter variable currRow:

Dim i As Integer, iPos As Integer, c As Integer, iRow As Integer, d As Integer, iShl As Integer
Dim currRow As Integer    

iPos = Worksheets("Sheet1").Cells(3, "J").Value
iShl = Worksheets("Sheet1").Cells(3, "I").Value
iRow = Worksheets("Sheet1").Cells(3, "H").Value
currRow = 1

For i = 1 To iPos
    For d = 1 To iShl
        For c = 1 To iRow
            Cells(currRow, 3).Value = i
            Cells(currRow, 2).Value = d
            Cells(currRow, 1).Value = c

            currRow = currRow + 1
        Next c
    Next d
Next i

Thanks for all you help. It is not cycling through all the numbers. It is only doing one iteration for the outermost loop and then stopping. Thoughts? — user3749080, Jun 18 '14 at 15:23

score 0 · Accepted Answer · answered Jun 18 '14 at 15:30

Your logic is a just a bit off here. First off, you want to write out all your row data in the innermost layer of your nested loops. You also will need a variable to keep track of how many rows you've already written (in the original code, the data will just repeatedly write over itself). Throw those changes in along with a couple other minor adjustments and you get something like this, which I believe accomplishes what you are looking for:

Sub generateValues()
    Dim i As Integer, d As Integer, c As Integer
    Dim iPos As Integer, iShl As Integer, iRow As Integer
    Dim rowCounter As Long
    iPos = Worksheets("Sheet1").Cells(3, "J").Value
    iShl = Worksheets("Sheet1").Cells(3, "I").Value
    iRow = Worksheets("Sheet1").Cells(3, "H").Value
    rowCounter = 1
    For i = 1 To iPos
        For d = 1 To iShl
            For c = 1 To iRow
                Cells(rowCounter, 1).Value = i
                Cells(rowCounter, 2).Value = d
                Cells(rowCounter, 3).Value = c
                rowCounter = rowCounter + 1
            Next c
        Next d
    Next i
End Sub

Triple Loop in VBA

2 Answers2