Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over undefined number of variables
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over undefined number of variables
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over undefined number of variables
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over undefined number of variables
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over undefined number of variables
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over undefined number of variables
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over undefined number of variables
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over undefined number of variables
On Jul 6, 3:36*pm, Matthew Herbert wrote:
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....ead/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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over undefined number of variables
On Jul 10, 12:59*pm, Question wrote:
On Jul 6, 3:36*pm, Matthew Herbert wrote: 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..../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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting the number of two different variables | Excel Discussion (Misc queries) | |||
How to make a public function procedure with undefined number of r | Excel Programming | |||
How to go "X" number of days out from a undefined date? | Excel Discussion (Misc queries) | |||
Linking Worksheets in an undefined number of Workbooks | Excel Discussion (Misc queries) | |||
How do I add the number of variables? | Excel Discussion (Misc queries) |