On Jul 2, 12:38*pm, Question wrote:
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....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