0

I am trying to create a recursive function in VBA to print out permutations of values in a spreadsheet. I saved all the values that need permutation into one x-dimensional array (array of arrays):

paramValues = [
    ['AA', 'BA', 'IA', '8A'],
    ['A', 'B', 'C'],
    ['1', '2']
]

Important: this is not a known array (in any size), so a recursive function will be needed.

Also, I have another array that tells me the dimension on each sub-array (how many values are in each array): paramControl = [4, 3, 2]

...

Now I'm trying to create something that looks like this:

Screenshot of desired result

but I'm stuck. I wrote three different codes, and I'm now trying to ignore the recursive aspect and spell out each individual For cycle.

This is what I have so far, but it's far from working properly - two main issues being: 1. Iteration through the arrays isn't working properly 2. Movements in the sheet are just... wrong!

For i = 1 To paramet

    For k = 1 To paramControl(i)

        ActiveCell.Value = paramValues(i, k)

            ' Print subsequent values
            For j = (i + 1) To paramet

                ActiveCell.Offset(0, 1).Range("A1").Select  ' Move right to fill second column

                For w = 1 To paramControl(j)

                    ActiveCell.Value = paramValues(j, w)

                    For y = (i + 2) To paramet

                        ActiveCell.Offset(0, 1).Range("A1").Select  ' Move right to fill second column

                        For p = 1 To paramControl(j)

                            ActiveCell.Value = paramValues(y, p)

                            ActiveCell.Offset(1, 0).Range("A1").Select ' Move down to fill second column


                        Next p

                    Next y

                    ActiveCell.Offset(0, -1).Range("A1").Select  ' Move left to fill second column

                Next w

                ActiveCell.Offset(0, -2).Range("A1").Select  ' Move left to fill second column


            Next j

        Cells(ActiveCell.Row, 1).Select

        ActiveCell.Offset(1, 0).Range("A1").Select ' Move down to fill second column

    Next k

    ActiveCell.Offset(1, 0).Range("A1").Select  ' Move down to fill second column

Next i

Can someone please help shade some light? I feel I'm getting stuck with high school problems! :)

I've also found this post which helped in understanding some different approach, but the underlying problem is slightly different. VBA recursive "For loops" Permutation?

Thanks

Community
  • 1
  • 1
M Wasabi Z
  • 31
  • 7
  • You don't need recursion for this - see e.g. https://stackoverflow.com/questions/19780016/vba-write-all-possible-combinations-of-4-columns-of-data/19780307#19780307 – Tim Williams Jun 28 '18 at 16:39
  • Also [this answer](https://stackoverflow.com/a/16988082/380384) might be of help to you also. – John Alexiou Jun 28 '18 at 19:22
  • @TimWilliams thanks, I am a novice with VBA so I can't say I fully understood the code in that answer, but I'm now adapting my code to work with it. This did the trick! :) – M Wasabi Z Jun 29 '18 at 12:43

1 Answers1

0

I'm not sure I follow all of that but will this help?

Dim paramValues  As Variant, i As Long, j As Long

paramValues = Array(Array("AA", "BA", "IA", "8A"), _
                    Array("A", "B", "C"), _
                    Array(1, 2))

For i = LBound(paramValues, 1) To UBound(paramValues, 1)
    For j = LBound(paramValues(i), 1) To UBound(paramValues(i), 1)
        Debug.Print paramValues(i)(j)
    Next j
Next i

'results in Immediate window
AA
BA
IA
8A
A
B
C
 1 
 2

You might also be interested in Expanding column cells for each column cell.

  • Hi Jeeped, thanks but this does not exactly do what I needed - I was looking at something more along the lines of this: https://i.stack.imgur.com/9Xfms.png – M Wasabi Z Jun 29 '18 at 12:42