创建VBA循环，用于包含字符串搜索和SUMIFS公式的公式。

Question

我有一个公式，它在另一个工作表上搜索列标题，一旦找到它，就会执行一些sumifs。我的数据大致如下：

    1                          8            10                      11         12
Column E  ...  Column N ... Column O ... Column AB   Column AC   Column AD   Column AE

Existing        CCS           data         100.00      100.00     120.00      150.00

列名称上方的数字很重要，我用它们来指定与我的公式相关的列。现在，循环中只有很少的单元格是重要的。单元格A1具有我想要终止的列号。单元格B1具有我想要从哪一列开始的列号。因此，如果我选择 A1=8 和 B1=1，我希望下面的代码通过搜索第 1 列，然后是第 2 列，一直到第 8 列，并将每个循环中的所有值相加，并将总和给出。以上表为例，如果我将 A1="12" 和 B1="10"，我希望它输出 370.00。

我目前使用的公式是：

=SUMIFS(INDEX('Program Data'!$A:$GA,0,MATCH(A1,'Data'!$1:$1,0)),'Data'!$N:$N,"CCS", 'Data'!$E:$E,"Existing")+SUMIFS(INDEX('Data'!$A:$GA,0,MATCH(A1,'Data'!$1:$1,0)), 'Data'!$N:$N,"CCS",'Data'!$E:$E,"Deep")

上述公式只给出包含A1中数字字符串的列的总和，但我需要所有包含B1 -> A1字符串的列的总和。

编辑

按照要求，这是我的循环失败尝试。由于B1本质上总是一个静态数字（即它将始终是45或88或其他），我认为我可以创建一个循环，告诉它从B1的任何值开始，并在需要时创建并放置公式到单元格中。所以对于这个，B1=1=z...

Sub LoopingYTD()
Dim z As Integer: z = 1
Dim formulaString2 As String
Do Until z = Range("A1") + 1
    If formulaString2 = "" Then
        formulaString2 = "="
    Else
        formulaString2 = formulaString2 & " + "
    End If

    formulaString2 = formulaString2 + " SUMIFS(INDEX('Data'!A:GA,0,MATCH("
    formulaString2 = formulaString2 & """*"" & 
    formulaString2 = formulaString2 & "z"&" ""
    formulaString2 = formulaString2 & """*""" & ",'Data'!1:1,0)),"
    formulaString2 = formulaString2 & "'Data'!$N:$N,”CCS”,”        
    formulaString2 = formulaString2 & “'Data'!$E:$E,”Existing”))"

    formulaString2 = formulaString2 & "+ SUMIFS(INDEX('Data'!A:GA,0,MATCH("
    formulaString2 = formulaString2 & """*"" & 
    formulaString2 = formulaString2 & "z"&" ""
    formulaString2 = formulaString2 & """*""" & ",'Data'!1:1,0)),"
    formulaString2 = formulaString2 & "'Data'!$N:$N,”CCS”,”        
    formulaString2 = formulaString2 & “Data'!$E:$E, ”Deep”))"
    z = z + 1
Loop
Range("B20").Value = formulaString2

我一直收到一个“应用程序定义或对象定义错误”的错误提示，但我不确定如何解决。

这是基于我之前提出的问题的建议。从那时起，我一直在尝试简化它，因为那有点混乱，所以我只想搜索一个数字字符串。基本上运行链接中的代码，但是不使用基于几个单元格的长而复杂的字符串，我只想让它计数，直到达到A1中的值。

- Kara

---

请展示一下你尝试过什么？询问代码的问题必须表现出对所解决问题的基本理解。包括尝试的解决方案、为什么它们不起作用以及预期结果。另请参阅：Stack Overflow问题清单。 - Siddharth Rout

好的Siddarth，谢谢你的清单 -- 我会尝试更好地澄清。 - Kara

这些数字与月份有关吗？看起来像是财务数据... - Mike L

是的，它们是！基本上是将月份相加，但它们不是相邻的，所以我正在尝试使用这种编号方案来表示递增的月份。 - Kara

Answer 1

如果B始终是一个静态数字，只需创建一个列来保存从B开始计数的所有可能与月份相关的数字。因此，如果B=20，且A <=32，则该列将如下所示（假设这是AD列）：

AC   AD 
Jan  20
Feb  21
...
Dec  32

然后创建一个公式，使用AC列中的月份标识符将这些数字与您在A1中选择的数字进行比较，如果A1≤（AD列中的值），则将其求和。该公式可能会有点长，但是看起来像这样：

=SUM(
 (SUMIFS(INDEX('Program Data'!$A:$GA,0,MATCH($AD1,'Data'!1:1,0)),   
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Existing")+SUMIFS(INDEX(' 
   'Data'!$A:$GA,0,MATCH($AD1,'Data'!1:1,0)),'Data'!$N:$N,"CCS", 
   'Data'!$E:$E,"Deep"))*(1<=MATCH($A$1,$AC$1:$AC$12,0)),
 (SUMIFS(INDEX('Data'!$A:$GA,0,MATCH($AD2,'Program Data'!1:1,0)),   
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Existing")+SUMIFS(INDEX(' 
   'Data'!$A:$GA,0,MATCH($AD2,'Data'!1:1,0)),'Data'!$N:$N,"CCS",
   'Data'!$E:$E,"Deep"))*(2<=MATCH($A$1,$AC$1:$AC$12,0)),
 (SUMIFS(INDEX('Data'!$A:$GA,0,MATCH($AD3,'Data'!1:1,0)), 
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Existing")+SUMIFS(INDEX(
   'Data'!$A:$GA,0,MATCH($AD3,'Data'!1:1,0)),'Data'!$N:$N,"CCS", 
   'Data'!$E:$E,"Deep"))*(3<=MATCH($A$1,$AC$1:$AC$12,0)),
  ....,....,....
 (SUMIFS(INDEX('Data'!$A:$GA,0,MATCH($AD12,'Data'!1:1,0)),
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Existing")+SUMIFS(INDEX( 
   'Data'!$A:$GA,0,MATCH($AD12,'Data'!1:1,0)), 
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Deep"))*(12<=MATCH($A$1,$AC$1:$AC$12,0)))

A1是你希望停止的数字。注意，你需要将语句末尾的数字更改为每个递增月份的数字（1-12，而不是分配的数字）。这样看起来对用户更好（用户可能无法理解这些数字），并且仍会输出所需的结果。

- Mike L