Excel VBA中自动筛选的动态嵌套循环 [英] Dynamic Nested Loops for Autofilter in Excel VBA

问题描述

我有一个程序设置来吐出特定的摘要.现在，我要使用该程序，并允许它接受用户输入的范围.我将其降低到2个级别，但从概念上讲，尽管我正在使用3D数组存储值，但要深入研究仍然很困难.

远离我的工作计算机，但类似的东西，

  Dim array1(3)As String'使用一些值填充它，例如Group1，Group2，Group3 ...Dim array2(4)As String'使用诸如NY，PA，...，Total之类的某些值再次进入下一个级别Dim arVal(3,5,4)as Long'对于每个组1，它将为arr2中的每个项目汇总5个字段对于我= LBound(array1)到UBound(array1)基于array1的自动过滤器对于j = LBound(array2)到UBound(array2)如果不是j = 4基于array2的自动过滤器万一arVal(i，0，j)=值arVal(i，1，j)=值...下一个j接下来我 

我的一个想法是使用链接列表，其中将有第一个列表，每个后续列表将链接到列表中的每个节点.唯一的问题是，我只是从概念上了解了C ++中的链表，因此尽管这可能是一个解决方案，但我不知道如何在VBA中实现它.

添加:

array1 = {Group 1，Group 2，.... Total}
array2 = {东北，西，东南....
arVal(1，1，1)=对于东北地区第1组中的所有人，购买的土豆总和(值1)
arVal(1，1，2)=对于东北地区第1组内的所有人员，唯一的人数(值2)

然后继续

arVal(1，2，1)=对于西部地区第1组中的所有人，马铃薯总和....
这样，对于每个组，我想总结一下该组中的每个区域.

如果我想在区域下动态添加更多子类别，则会出现问题.

所以我想主要的问题是，如果给定一个整数，则创建一个等于该整数的嵌套循环，如下所示:

 如果i = 2{对于组中的每个项目对于区域中的每个项目一些功能}如果我= 3{对于组中的每个项目对于区域中的每个项目对于NextSubcategory中的每个项目一些功能} 

尽管我可以限制使用switch语句可以使用的子类别的数量，但是如果程序是动态的，则程序会更健壮.如果我尝试使用多维数组来存储值，还要加上即将来临的动脉瘤.

解决方案

在VBA中的链接列表主题上，这可能对您有用.

http://www.cpearson.com/excel/classes.aspx

然后，您可以按照在C ++中使用的方法，使用VBA类创建链接列表.

这可能也有帮助链接

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.

Away from my work computer but its something like,

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

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.

Addition:

array1 = {Group 1, Group 2, .... Total}
array2 = {Northeast, West, Southeast....}
arVal(1, 1, 1) = For all people within Group 1 in the Northeast Region the Sum of Potatoes bought(Value 1)
arVal(1, 1, 2) = For all people within Group 1 in the Northeast Region the unique count of people(Value 2)

and then it goes on to

arVal(1, 2, 1) = For all people within Group 1 in the WEST region the Sum of Potatoes....
and so forth such that for each group, I want to summarize each of the regions within that group.

The problem arises if I want to dynamically add more subcategories under the regions.

So I guess the main question is if given an integer, to create a nested loop equal to that integer such that:

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
}

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.

解决方案

On the topic of Linked lists in VBA this may be useful to you.

http://www.cpearson.com/excel/classes.aspx

You could then follow the methodology you used in C++ to create a linked list using the VBA class.

Edit: this may also help Link

这篇关于Excel VBA中自动筛选的动态嵌套循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！