Databases
StackOverflow2013
Postsdbo
PODynamic Nested For Loops
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODynamic Nested For Loops
    text
    copied!<p>First off, I apologize for starting a new thread but the original got confusing because I couldn't articulate my ask well (Link to original thread: <a href="https://stackoverflow.com/questions/7155905/dynamic-nested-loops-for-autofilter-in-excel-vba?answertab=active#tab-top">Dynamic Nested Loops for Autofilter in Excel VBA</a>). But now I have actually written the program to the way I like except using a switch statement instead of the more dynamic use of nested looping. </p> <p><strong>edit:</strong><br> RSum is used to store a range and a boolean. The user selects the header cell for a column and chooses whether they want get a summation of that column or a unique count when summarizing. This allows for a collection of these objects to allow summarizing of multiple columns. This input wasn't so bad to make dynamic. The next input which starts as rtemp and ends as array1, is again the user selects the header cell for a column but this it takes the values in that column and saves a unique list to array1. With this list a for loop loops through the array using its value as criteria for an autofilter. For each step in the loop after the autofilter, the summary is calculated using the SumThisA taking the RSum object collection as an input. The data is laid out in columns where each row is a unique record.</p> <p>So the question is, for the below code, I want the user to be able to select the number of categories to summarize by, have a popup to fill in those ranges (can figure this out), and then run a filter as such:</p> <pre><code>for i = 0 to UBound(array1) Autofilter criteria1:=array1(i) for j = 0 to UBound(array2) Autofilter criteria1:=array2(j) ...... for x = 0 to UBound(arrayx) Autofilter criteria1:=arrayx(x) aSum(i,j,....x) = somefunction </code></pre> <p>Now I understand I would need to use a recursive function, but having never used one before and the somewhat complexity of this program, it is out of my understanding. Would anyone be able to help explain how to use it in this context? Plus because of the generalization of this program, it could be a useful tool for many people. </p> <pre><code>'---------Initialize Arrays---------------' t = sMax - 1 Dim aSum() ReDim aSum(UBound(arr1), t) '---------------------Perform Summary----------------' For i = LBound(arr1) To UBound(arr1) If i = 0 Then Data.AutoFilter field:=afield, Criteria1:=arr1, Operator:=xlFilterValues Else Data.AutoFilter field:=afield, Criteria1:=arr1(i) End If temp = SumThisA(SumValues, sMax) For j = LBound(temp) To UBound(temp) aSum(i, j) = temp(j) Next j Next i </code></pre> <p>Sum of Dollars For:<br> 1. arrayA(1)-------100<br> - arrayB(1)------30<br> - arrayB(2)------70<br> 2. arrayA(2)-------200<br> - arrayB(1)-----120<br> - arrayB(2)------80<br> 3. Total-----------300</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