StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODynamic Nested Loops for Autofilter in Excel VBA
text
Body
copied!<p>I have a program setup to spit out a specific summary. Now I want to take that program and allow it to accept user inputted ranges. I have it down to 2 levels but it gets conceptually hard for me to go further, though I am using a 3D array to store the values.</p> <p>Away from my work computer but its something like,</p> <pre><code>Dim array1(3) As String 'Fill it with some values like Group1, Group2, Group3... Dim array2(4) As String 'Again the next level with some values like NY, PA,..., Total Dim arVal(3,5,4) as Long 'For each Group 1, it will summarize 5 fields for each item in arr2 For i = LBound(array1) To UBound(array1) Autofilter based on array1 For j = LBound(array2) to UBound(array2) If Not j = 4 Autofilter based on array2 EndIf arVal(i, 0, j) = Value arVal(i, 1, j) = Value ... Next j Next i </code></pre> <p>One idea I have is to use linked lists, where there will be the 1st list where each subsequent list will chain to each node in the list. Only problem is i only conceptually learned about linked lists in C++ so while this may be a solution I have no clue on how to implement this in VBA.</p> <p><strong>Addition:</strong></p> <blockquote> <p>array1 = {Group 1, Group 2, .... Total}<br> array2 = {Northeast, West, Southeast....}<br> arVal(1, 1, 1) = For all people within Group 1 in the Northeast Region the Sum of Potatoes bought(Value 1)<br> arVal(1, 1, 2) = For all people within Group 1 in the Northeast Region the unique count of people(Value 2) </p> </blockquote> <p>and then it goes on to </p> <blockquote> <p>arVal(1, 2, 1) = For all people within Group 1 in the WEST region the Sum of Potatoes....<br> and so forth such that for each group, I want to summarize each of the regions within that group. </p> </blockquote> <p>The problem arises if I want to dynamically add more subcategories under the regions. </p> <p>So I guess the main question is if given an integer, to create a nested loop equal to that integer such that:</p> <pre><code>If i = 2 { For Each item in Group For Each item in Regions some function } If i = 3 { For Each item in Group For Each item in Regions For Each item in NextSubcategory some function } </code></pre> <p>Though I could limit the amount of subcategories one could go and use a switch statement, the program would be more robust if dynamic. Plus the impending aneurysm if I try to use multidimensional arrays to store the values.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload