为包含字符串搜索和sumifs的公式创建VBA循环

I have a formula that searches for a column header on another sheet, then once its found it goes through some sumifs. My data roughly looks like this:

我有一个公式,在另一张纸上搜索列标题,然后一旦发现它经过一些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

The numbers above the column names are significant and I use them to designate columns that are relevant to my formulas. Now, There are really only a few cells that are significant to the loop. Cell A1 has the number of the column I want to terminate on. Cell B1 has the number of the column I want to start from. So If I choose A1=8 and B1=1, I want the below code to run the sumifs starting by searching for column 1, then for column 2, all the way until column 8 and add all of the values from each loop together and give me the total. Using the above table as an example, if I set A1="12" and B1="10", I want it to spit out 370.00.

列名称上方的数字很重要,我用它们来指定与我的公式相关的列。现在,实际上只有少数单元对循环很重要。单元格A1具有我想要终止的列号。单元格B1具有我想要从中开始的列的编号。所以如果我选择A1 = 8和B1 = 1,我希望下面的代码运行sumifs,首先搜索第1列,然后是第2列,直到第8列,并将每个循环的所有值加在一起,给我总数。以上表为例,如果我设置A1 =“12”且B1 =“10”,我希望它吐出370.00。

The formula I currently have is this:

我现在的公式是这样的:

=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")

= SUMIFS(INDEX('节目数据'!$ A:$ GA,0,MATCH(A1,'数据'!$ 1:$ 1,0)),'数据'!$ N:$ N,“CCS”,'数据'!$ E:$ E,“现有”)+ SUMIFS(INDEX('数据'!$ A:$ GA,0,MATCH(A1,'数据'!$ 1:$ 1,0)),'数据'!$ N:$ N, “CCS”, '数据' $ E:$ E, “深”)

The formula above only gives me the total for the column that contains the numerical string in A1, but I need a total for all columns with the strings from B1 -> A1.

上面的公式只给出了包含A1中数字字符串的列的总数,但我需要一个包含B1 - > A1字符串的所有列的总数。

EDIT

As requested, here is my failing attempt at this loop. Since B1 is essentially always a static number (meaning it will always be 45 or 88 or something), I thought I could create a loop telling it to start at whatever value B1 is and essentially create and drop the formula into the cell as I need it. So for this one, B1=1=z...

根据要求,这是我在这个循环中失败的尝试。由于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

I keep getting an "application-defined or object defined error", though I'm not sure what the remedy is for that.

我不断得到“应用程序定义或对象定义的错误”,但我不确定是什么补救措施。

This is based off of a suggestion from an earlier question I asked. Since then, I've been trying to simplify it, as that was a bit messy, so I just want it to search for a single string of numbers. Basically run the code in the link, but instead of some long, convoluted string based on several cells, I just want it to count up until it reaches the value in A1.

这是基于我问过的早期问题的建议。从那以后,我一直在努力简化它,因为它有点乱,所以我只想让它搜索一串数字。基本上在链接中运行代码,但是基于几个单元格而不是一些冗长的,错综复杂的字符串,我只是希望它计数直到达到A1中的值。

1 个解决方案

#1

Well, if B is always a static number, just create a column that holds the possible numbers starting at B and counting up for all possible numbers attached to a month. So if B=20, and A <=32, then the column will look like this (lets say this is column AD):

好吧,如果B始终是静态数字,只需创建一个列,其中包含从B开始的可能数字,并计算附加到一个月的所有可能数字。因此,如果B = 20,A <= 32,那么该列将如下所示(假设这是列AD):

AC   AD 
Jan  20
Feb  21
...
Dec  32

Then create a formula that compares these numbers to the number you chose in A1 using the Month signifier in AC, and if A1<= (value in column AD), then sum it in. The formula will be kinda long, but will look like this:

然后创建一个公式,使用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)))

Where A1 = the number you want to stop at. Note that you change the number for the matches at the end of the statement to each ascending month number (1-12, not the numbers assigned). This way it looks good for the user (who wouldn't necessarily understand the numbers anyway) and will still output your desired result.

其中A1 =您想要停留的数字。请注意,您将语句末尾的匹配数更改为每个递增的月份数(1-12,而不是分配的数字)。这样它对用户来说看起来很好(无论如何都不一定了解数字)并且仍然会输出您想要的结果。