I have a problem I can't seem to find a solution to, so hopefully
someone has some ideas...

I found a problem similar to mine with a solution that gets me part of
the way there, see the below link:

http://www.excelforum.com/excel-prog...excel-vba.html

I would like to be able to take table, such as...

Variable

Variable 1(Shirt size) [small;medium;large;...;Y]
Variable 2(shirt color)[blue;green;red;....;Z]
....
variable X (...)

When the number of variables is fixed, the solution seems to just be
the correct number of for...next loops. However, when the number of
variables is undefined; I run into a problem. I need the code to be
flexible enough to adjust for the number of variables.

I know I can build an array to pick up the number of variables, and
also the number of selections within each variable, but once I've got
that, I'm stuck; since the only option I can think of is the hard-
coded for/next loops.

Ideas?

On Jul 1, 10:57*am, Question wrote:
I have a problem I can't seem to find a solution to, so hopefully
someone has some ideas...

I found a problem similar to mine with a solution that gets me part of
the way there, see the below link:

http://www.excelforum.com/excel-prog...te-programmati...

I would like to be able to take table, such as...

Variable

Variable 1(Shirt size) [small;medium;large;...;Y]
Variable 2(shirt color)[blue;green;red;....;Z]
...
variable X (...)

When the number of variables is fixed, the solution seems to just be
the correct number of for...next loops. However, when the number of
variables is undefined; I run into a problem. I need the code to be
flexible enough to adjust for the number of variables.

I know I can build an array to pick up the number of variables, and
also the number of selections within each variable, but once I've got
that, I'm stuck; since the only option I can think of is the hard-
coded for/next loops.

Ideas?

Question,

Have you read through any of the help documentation on arrays? Within
VBE search the following:
ReDim (read about Preserve)
LBound (pay attention to the dimension parameter)
UBound (pay attention to the dimension parameter)

I'm anticipating that you are looking for a For Next loop that
references the array's bounds, such as the following:

For I = LBound(myArr,2) to UBound(myArr,2)
'Code
Next I

Best,

Matthew Herbert

On Jul 1, 1:51*pm, Matthew Herbert wrote:
On Jul 1, 10:57*am, Question wrote:



I have a problem I can't seem to find a solution to, so hopefully
someone has some ideas...

I found a problem similar to mine with a solution that gets me part of
the way there, see the below link:

http://www.excelforum.com/excel-prog...te-programmati...

I would like to be able to take table, such as...

Variable

Variable 1(Shirt size) [small;medium;large;...;Y]
Variable 2(shirt color)[blue;green;red;....;Z]
...
variable X (...)

When the number of variables is fixed, the solution seems to just be
the correct number of for...next loops. However, when the number of
variables is undefined; I run into a problem. I need the code to be
flexible enough to adjust for the number of variables.

I know I can build an array to pick up the number of variables, and
also the number of selections within each variable, but once I've got
that, I'm stuck; since the only option I can think of is the hard-
coded for/next loops.

Ideas?

Question,

Have you read through any of the help documentation on arrays? *Within
VBE search the following:
ReDim (read about Preserve)
LBound (pay attention to the dimension parameter)
UBound (pay attention to the dimension parameter)

I'm anticipating that you are looking for a For Next loop that
references the array's bounds, such as the following:

For I = LBound(myArr,2) to UBound(myArr,2)
'Code
Next I

Best,

Matthew Herbert

Matt,

Thanks for the response. I am familiar with arrays.

The way the code works now is using multiple embedded For loops, bound
by the array size for each variable

For i = lbound(it1) to ubound(it1)
For i = lbound(it2) to uibound(it2)
For i = lbound(it3) to ubound(it3).
'code
next
next
next

the problem is I am having to create a "for" loop for each additional
variable I want - not a problem if I had a fixed number of variables,
but the variables themselves need to be flexible in count - I can't
figure out how to eliminate the need for the embedded loops. I can
post the full code if necessary.

On Jul 1, 11:58*am, Question wrote:
On Jul 1, 1:51*pm, Matthew Herbert wrote:





On Jul 1, 10:57*am, Question wrote:

I have a problem I can't seem to find a solution to, so hopefully
someone has some ideas...

I found a problem similar to mine with a solution that gets me part of
the way there, see the below link:

http://www.excelforum.com/excel-prog...te-programmati....

I would like to be able to take table, such as...

Variable

Variable 1(Shirt size) [small;medium;large;...;Y]
Variable 2(shirt color)[blue;green;red;....;Z]
...
variable X (...)

When the number of variables is fixed, the solution seems to just be
the correct number of for...next loops. However, when the number of
variables is undefined; I run into a problem. I need the code to be
flexible enough to adjust for the number of variables.

I know I can build an array to pick up the number of variables, and
also the number of selections within each variable, but once I've got
that, I'm stuck; since the only option I can think of is the hard-
coded for/next loops.

Ideas?

Question,

Have you read through any of the help documentation on arrays? *Within
VBE search the following:
ReDim (read about Preserve)
LBound (pay attention to the dimension parameter)
UBound (pay attention to the dimension parameter)

I'm anticipating that you are looking for a For Next loop that
references the array's bounds, such as the following:

For I = LBound(myArr,2) to UBound(myArr,2)
'Code
Next I

Best,

Matthew Herbert

Matt,

Thanks for the response. I am familiar with arrays.

The way the code works now is using multiple embedded For loops, bound
by the array size for each variable

For i = lbound(it1) to ubound(it1)
For i = lbound(it2) to uibound(it2)
For i = lbound(it3) to ubound(it3).
'code
next
next
next

the problem is I am having to create a "for" loop for each additional
variable I want - not a problem if I had a fixed number of variables,
but the variables themselves need to be flexible in count - I can't
figure out how to eliminate the need for the embedded loops. I can
post the full code if necessary.- Hide quoted text -

- Show quoted text -

Question,

Yes, you'll need to post the code in order for someone to decipher how
you are loading your arrays and how you want to manipulate or access
your arrays.

Best,

Matt

On Jul 1, 3:36*pm, Matthew Herbert wrote:
On Jul 1, 11:58*am, Question wrote:



On Jul 1, 1:51*pm, Matthew Herbert wrote:

On Jul 1, 10:57*am, Question wrote:

I have a problem I can't seem to find a solution to, so hopefully
someone has some ideas...

I found a problem similar to mine with a solution that gets me part of
the way there, see the below link:

http://www.excelforum.com/excel-prog...te-programmati...

I would like to be able to take table, such as...

Variable

Variable 1(Shirt size) [small;medium;large;...;Y]
Variable 2(shirt color)[blue;green;red;....;Z]
...
variable X (...)

When the number of variables is fixed, the solution seems to just be
the correct number of for...next loops. However, when the number of
variables is undefined; I run into a problem. I need the code to be
flexible enough to adjust for the number of variables.

I know I can build an array to pick up the number of variables, and
also the number of selections within each variable, but once I've got
that, I'm stuck; since the only option I can think of is the hard-
coded for/next loops.

Ideas?

Question,

Have you read through any of the help documentation on arrays? *Within
VBE search the following:
ReDim (read about Preserve)
LBound (pay attention to the dimension parameter)
UBound (pay attention to the dimension parameter)

I'm anticipating that you are looking for a For Next loop that
references the array's bounds, such as the following:

For I = LBound(myArr,2) to UBound(myArr,2)
'Code
Next I

Best,

Matthew Herbert

Matt,

Thanks for the response. I am familiar with arrays.

The way the code works now is using multiple embedded For loops, bound
by the array size for each variable

For i = lbound(it1) to ubound(it1)
For i = lbound(it2) to uibound(it2)
For i = lbound(it3) to ubound(it3).
'code
next
next
next

the problem is I am having to create a "for" loop for each additional
variable I want - not a problem if I had a fixed number of variables,
but the variables themselves need to be flexible in count - I can't
figure out how to eliminate the need for the embedded loops. I can
post the full code if necessary.- Hide quoted text -

- Show quoted text -

Question,

Yes, you'll need to post the code in order for someone to decipher how
you are loading your arrays and how you want to manipulate or access
your arrays.

Best,

Matt

Here's what I've got:

For set1 = 1 To itemRange.Cells.Count 'set 1 item selection
For set2 = 1 To itemRange.Cells.Count 'set 2 item selection
For set3 = 1 To itemRange.Cells.Count 'set 3 item selection
iterationCount = iterationCount + 1
'other code which uses the range and set info to calculate
some stuff unrelated to the iteration process
Next
Next
Next

itemRange just tells me how many items are in the largest set, so it
knows how many times to loop through a set is populated by looking at
a small table in excel (range E10:I12). Range E10:E12 is required to
not be null, and anything column F:I is a new item within the set.
Range C10:C12 is where I have my set names. Currently, my code is set
up for 3 sets (each set gets an for/next loop above). I am able to
pass my list of sets into excel using the same technique as I use for
the items, but I don't know how to use that array to improve this
situation above.

I tried doing an loop that said;
For setsArray = lbound(setsArray) to ubound(setsArray)
For itemRange = 1 to itemRange.cells.count
iterationCount = iterationCount + 1
Next
Next

But that only gave me iterations where 1 set varied, while the other
sets stayed constant.

If I fully utilize my table, I should have 3 sets, and 5 items in each
set, which gives me a total of 243 combinations (3^5). My problem is I
want both the number of items and the number of sets to vary. I plan
on letting the user input the number of sets, and number of items, and
want it to iterate based on the user input.

On Jul 2, 2:19*pm, Question wrote:
On Jul 1, 3:36*pm, Matthew Herbert wrote:



On Jul 1, 11:58*am, Question wrote:

On Jul 1, 1:51*pm, Matthew Herbert wrote:

On Jul 1, 10:57*am, Question wrote:

I have a problem I can't seem to find a solution to, so hopefully
someone has some ideas...

I found a problem similar to mine with a solution that gets me part of
the way there, see the below link:

http://www.excelforum.com/excel-prog...te-programmati...

I would like to be able to take table, such as...

Variable

Variable 1(Shirt size) [small;medium;large;...;Y]
Variable 2(shirt color)[blue;green;red;....;Z]
...
variable X (...)

When the number of variables is fixed, the solution seems to just be
the correct number of for...next loops. However, when the number of
variables is undefined; I run into a problem. I need the code to be
flexible enough to adjust for the number of variables.

I know I can build an array to pick up the number of variables, and
also the number of selections within each variable, but once I've got
that, I'm stuck; since the only option I can think of is the hard-
coded for/next loops.

Ideas?

Question,

Have you read through any of the help documentation on arrays? *Within
VBE search the following:
ReDim (read about Preserve)
LBound (pay attention to the dimension parameter)
UBound (pay attention to the dimension parameter)

I'm anticipating that you are looking for a For Next loop that
references the array's bounds, such as the following:

For I = LBound(myArr,2) to UBound(myArr,2)
'Code
Next I

Best,

Matthew Herbert

Matt,

Thanks for the response. I am familiar with arrays.

The way the code works now is using multiple embedded For loops, bound
by the array size for each variable

For i = lbound(it1) to ubound(it1)
For i = lbound(it2) to uibound(it2)
For i = lbound(it3) to ubound(it3).
'code
next
next
next

the problem is I am having to create a "for" loop for each additional
variable I want - not a problem if I had a fixed number of variables,
but the variables themselves need to be flexible in count - I can't
figure out how to eliminate the need for the embedded loops. I can
post the full code if necessary.- Hide quoted text -

- Show quoted text -

Question,

Yes, you'll need to post the code in order for someone to decipher how
you are loading your arrays and how you want to manipulate or access
your arrays.

Best,

Matt

Here's what I've got:

* * For set1 = 1 To itemRange.Cells.Count 'set 1 item selection
* * For set2 = 1 To itemRange.Cells.Count 'set 2 item selection
* * For set3 = 1 To itemRange.Cells.Count 'set 3 item selection
* * * * iterationCount = iterationCount + 1
* * * * 'other code which uses the range and set info to calculate
some stuff unrelated to the iteration process
* * Next
* * Next
* * Next

itemRange just tells me how many items are in the largest set, so it
knows how many times to loop through a set is populated by looking at
a small table in excel (range E10:I12). Range E10:E12 is required to
not be null, and anything column F:I is a new item within the set.
Range C10:C12 is where I have my set names. Currently, my code is set
up for 3 sets (each set gets an for/next loop above). I am able to
pass my list of sets into excel using the same technique as I use for
the items, but I don't know how to use that array to improve this
situation above.

I tried doing an loop that said;
For setsArray = lbound(setsArray) to ubound(setsArray)
For itemRange = 1 to itemRange.cells.count
*iterationCount = iterationCount + 1
Next
Next

But that only gave me iterations where 1 set varied, while the other
sets stayed constant.

If I fully utilize my table, I should have 3 sets, and 5 items in each
set, which gives me a total of 243 combinations (3^5). My problem is I
want both the number of items and the number of sets to vary. I plan
on letting the user input the number of sets, and number of items, and
want it to iterate based on the user input.

I found someone else who was much more elegant about asking this
question, albeit in matlab. The answer was given as well, although I
am not strong enough in VBA to know how to convert the answer to the
language...

http://groups.google.com/group/comp.... 55c1ffbedda71