Nesting For .. Next loops 1

[ab123456]

Say I had a number of different object and each of these objects could take a different value, how can I create a list of all possible combinations?

I think maybe an example is needed here, say I have one object called A that can take a value between 1 and 3 and object B which can be either 1 or 2, the possible combinations are
1,1
1,2
2,1
2,2
3,1
3,2

I can use nested For .. next loops for this example such as
For A = 1 to 3
For B = 1 to 2
Result = A & B
Next
Next

But the number of objects may be anything from 1 to 12 depending on the user input.

How can I change the number of nested loops depending on the user input?

 

[pmover]

use the inputbox function within vba and use the result as the upper limit of for-next loop.

resulta=inputbox( . . .)
resultb=inputbox( . . .)
for a=1 to resulta
for b=1 to resultb

hope this helps.

good luck!
-pmover

 

[ab123456]

Thanks pmover for your help but maybe i wasnt clear enough in my question. I can change the number of times it loops but what I up struggling to do is change the number of loops.

Example as well as objects A and B I may also have objects C and D (and beyond), so what i need to do

For A = 1 to resulta
For B = 1 to resultb
For C = 1 to resultc
For D = 1 to resultd

Thanks

 

[CajunCenturion]

This sounds like it may have to be a recursive process. Are you at all familar with recursion?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ab123456]

Cajuncenturion :

Not familiar at all!! can you offer any suggestions

Thanks

 

[ivymike]

you could have the inner loops deactivate (for x = 1 to 0 will result in no action, for example)

you could use a recursive subroutine as well, but they're trickier to get right.

 

[CajunCenturion]

Recursion can be done in VB and in VBA. Recursion is essentially a function which calls itself. I don't know enough about your problem to provide specifics, but something along the lines of the following:

Function DoALoop (LoopStart as Integer, LoopEnd as Integer) As String

   Dim RetVal As String
   RetVal = ""
   For Idx = LoopStart To LoopEnd
      If <some conditional> Then
         RetVal = RetVal & DoLoop(StartValue, EndValue)
      Else
         RetVal = RetVal & <local expression>
      End If
   Next Ix
   DoALoop = RetVal

End Function

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ab123456]

Solved this one using recursion, thanks everyone for all the help