Iterate over undefined number of variables

[Question]

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-pro...-for-choices-from-data-sets-in-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?

 

[Matthew Herbert]

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-programming/558555-create-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

 

[Question]

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-programming/558555-create-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.

 

[Matthew Herbert]

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-programming/558555-create-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

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

 

[Question]

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 partof
the way there, see the below link:
http://www.excelforum.com/excel-programming/558555-create-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.

 

[Question]

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-programming/558555-create-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 justbe
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'vegot
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...=alternative+to+nested+loops#baa55c1ffbedda71

 

[Matthew Herbert]

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-programming/558555-create-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.soft-sys.matlab/browse_thread/thr...-Hide quoted text -

- Show quoted text -

Question,

I think that I'm having a tough time actually understanding what you
are trying to do exactly. I'll create a hypothetical scenario to see
if I understand. Let's say you have a set of data contained in
Excel. That data is similar to a database, e.g. a shirt size is a one-
to-many relationship. Or in other words, a size (in column A) which
is "small" may have the colors (in column B), red, white, blue, etc.
and each color may have the patterns (in column C) basic, tiled,
swirl, etc. Are you trying to determine how to dynamically loop
through each element of size (column A) that is "small"? That is, for
each small, you would then loop through each color, and within each
color you would loop through each design? Then, after size = small,
you would move on to medium, etc.? Another way to say this is that
you want to loop through each A, for each A loop through B, and then
for each B loop through C. Once the first A is complete, you move on
to the next A. If we are not seeing eye to eye on this, then feel
free to email your spreadsheet to me and I can take a look at what you
are trying to accomplish.

Best,

Matt

 

[Question]

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-programming/558555-create-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 codeto 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.soft-sys.matlab/browse_thread/thr...Hide quoted text -

- Show quoted text -

Question,

I think that I'm having a tough time actually understanding what you
are trying to do exactly.  I'll create a hypothetical scenario to see
if I understand.  Let's say you have a set of data contained in
Excel.  That data is similar to a database, e.g. a shirt size is a one-
to-many relationship.  Or in other words, a size (in column A) which
is "small" may have the colors (in column B), red, white, blue, etc.
and each color may have the patterns (in column C) basic, tiled,
swirl, etc.  Are you trying to determine how to dynamically loop
through each element of size (column A) that is "small"?  That is, for
each small, you would then loop through each color, and within each
color you would loop through each design?  Then, after size = small,
you would move on to medium, etc.?  Another way to say this is that
you want to loop through each A, for each A loop through B, and then
for each B loop through C.  Once the first A is complete, you move on
to the next A.  If we are not seeing eye to eye on this, then feel
free to email your spreadsheet to me and I can take a look at what you
are trying to accomplish.

Best,

Matt

That is essentially it - loop through every element of each set, such
that when I am done looping, I have run through every possible
combination of the elements.. The part that is tricky, is that I need
the code to be dynamic, so that I can choose to loop through only two
sets (size, and color), or as many sets as I want (size, color,
design, texture). If I have a finite number of sets, I know I can just
embed the requisite number of "for/next" loops so that I achieve the
looping. The part that is tripping me up is that I want an indefinite
number of sets, which requires a technique that is a little more
flexible. Maybe I'm missing something obvious; but I'm not sure.

 

[Matthew Herbert]

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 getsme part of
the way there, see the below link:
http://www.excelforum.com/excel-programming/558555-create-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.soft-sys.matlab/browse_thread/thr....quoted text -
- Show quoted text -

Question,

I think that I'm having a tough time actually understanding what you
are trying to do exactly.  I'll create a hypothetical scenario to see
if I understand.  Let's say you have a set of data contained in
Excel.  That data is similar to a database, e.g. a shirt size is a one-
to-many relationship.  Or in other words, a size (in column A) which
is "small" may have the colors (in column B), red, white, blue, etc.
and each color may have the patterns (in column C) basic, tiled,
swirl, etc.  Are you trying to determine how to dynamically loop
through each element of size (column A) that is "small"?  That is, for
each small, you would then loop through each color, and within each
color you would loop through each design?  Then, after size = small,
you would move on to medium, etc.?  Another way to say this is that
you want to loop through each A, for each A loop through B, and then
for each B loop through C.  Once the first A is complete, you move on
to the next A.  If we are not seeing eye to eye on this, then feel
free to email your spreadsheet to me and I can take a look at what you
are trying to accomplish.

Best,

Matt

That is essentially it - loop through every element of each set, such
that when I am done looping, I have run through every possible
combination of the elements.. The part that is tricky, is that I need
the code to be dynamic, so that I can choose to loop through only two
sets (size, and color), or as many sets as I want (size, color,
design, texture). If I have a finite number of sets, I know I can just
embed the requisite number of "for/next" loops so that I achieve the
looping. The part that is tripping me up is that I want an indefinite
number of sets, which requires a technique that is a little more
flexible. Maybe I'm missing something obvious; but I'm not sure.- Hide quoted text -

- Show quoted text -

Question,

Sorry for the tardy reply. I think this can be solved in how the data
is presented in the worksheet, e.g. database style where every column
is a field and every row is a record. There are a couple of ways to
determine where your "anchors" are for the data. I'll list two of
many ways below, and I'll list my assumptions for each.

If your data is contiguous, starts in Range("A1"), and includes column
headers in row 1 then the following will exclude the header:

Dim Rng As Range
Set Rng = Range("A1").CurrentRegion
Set Rng = Rng.Resize(Rng.Rows.Count - 1, Rng.Columns.Count).Offset(1,
0)

If you want to find the last data point in a given column with row 1
including a header and at least 1 data point in row 2 the following
code will exclude the header:

Dim Rng As Range
Dim lngRows As Long

With ActiveSheet
lngRows = .Cells.Count
lngRows = .Cells(lngRows).Row
End With

Set Rng = Range("A2", Cells(lngRows, "A").End(xlUp))
'-OR-
Set Rng = Range("A2", Cells(lngRows, "C").End(xlUp))

In the above two ways, you create a range object (Rng) to work with.
You can create other range object from Rng (Set rngTwo = Rng.Columns
(2); Set rngTwo = Rng.Offset(0,3)), count rows and columns for loops
(For intI = 1 To Rng.Columns.Count or For lngL = 1 To Rng.Rows.Count),
or loop through each cell within the range object (For Each rngCell in
Rng.Cells -- note that this loop moves through columns then rows, e.g.
A1 then A2 then B1 then B2).

Without seeing your data, I hope that this sparks enough ideas for you
to work with. Otherwise, please explain how the data is setup. (For
example, Size in column A, Color in column B, Pattern in column C; 3
different sizes (Small, Medium, and Large), 4 different colors (Red,
Blue, Green, and Yellow), and 2 different patterns (Plain, and
Checkered); no blank cells in the data set; 15 rows of data (including
the header); the upper-left corner of the data begins in A1).

Best,

Matthew Herbert