This is an interesting problem and it lends itself to a recursive programming approach.
The code below uses a couple of procedures (ProcessArray and AddCodes) which call each other repeatedly while an array of possible combinations reduces in size until it is only one variable big. At this point, the line of values is written to the spreadsheet.
To keep things simpler for me, I’ve hard coded a set of variables (V1 to V5) and their possible states (S1 – Sn) . In the example, variable 1 has 4 states, variable 2 has 3, variable 3 has 4, variable 4 has 4 and finally variable 5 has five. This should result in 4x3x4x4x5 = 960 combinations.
You will need to adapt the code to fit in with the plans that you have to ask the user to define the number of variables and their possible states and get the positioning on the sheet the way that you want it.
I hope that this is useful.
Bookmarks