麻烦创建一个excel Do循环 [英] Trouble creating an excel Do Loop

问题描述

我有数据需要创建某种循环。基本上，我正在搜索一个列，我需要使用字符串搜索的信息，然后根据各种标准求和。我的问题是数据是以月份分隔的（使用数字名称，即Jan = 01，Feb = 02等），我需要一种方法将所有的信息加起来。我使用的公式如下：

  = SUMPRODUCT（SUMIFS（INDEX（'Program Data'！A：GA，0， （*& - &A8& B15&*，
'Program Data'！1：1,0）），程序数据'$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 
 $ b 
其中A8是包含所选择的感兴趣的月份的单元格。所以说A8 = 08（8月）。我想对所有以前的A8值执行这个公式，并将它们加在一起，向上加上 UNTIL  A8等于当前的选择。 （或者A8 <09，但是这可以实现）
 
 
 
所以假设A8 = 08，我希望它从A8 = 01开始，运行这个代码，找到值。然后运行A8 = 02，找到该值，然后将其添加到第一个值，继续这个，直到我们到达A8 = 08，然后报告最终值。由于单位数字是用两位数字（即01,02,03等）编写的，并且使用前导0进行搜索，所以我觉得它的循环账号相当重要，直到我们得到两位数的月份（10 ，11，12）.... 
 
 
 
沿着（这不是代码，只是一般的思考过程）的东西：
  Sub Loop1（）
 Dim x as integer 
x = 01 
直到x =单元格A8 
 （插入上面的公式，但使用& A8&= x）
x + 1 = x 
 End Sub 
  
但是，即使这个思想过程似乎并没有将结果公式值加在一起，所以我需要帮助。我不知道如何在excel中执行do循环，所以您衷心感谢您提供的任何帮助。恐怕我真的不知道从哪里开始，还是逻辑的顺序每一个声明。感谢您的光临！
 
 
 
 编辑 
 
 
 
请原谅我的描述不好，希望这个澄清：
 
 
 
代码是表的一部分。单元格A8是一个下拉选项，您可以在其中选择要检查的数字月份（01，02  - > 12）。然后公式使用一组引用单元格查找一个字符串，让我们说Rev Bud 08 -2013​​（其中08来自您的选择），然后查找具有该标题的列，然后执行sumifs。但是我想要一年到头，所以我希望它可以从x = 1开始为每个x优化公式，直到并包括在单元格A8中选择的任何值，然后将所有的一起执行，并在我选择的单元格中显示这个值，让我们说单元格C50。所以我想要从列上开始，标题为Rev Bud 01 -2013​​，然后是Rev Bud 02 -2013​​....一直到Rev Bud [我的选择] -2013​​，然后添加所有这些在一起。 
解决方案
我没有完全遵循公式如何改变的问题，而最终公式是什么细胞应该居住在这里，但这里是如何构造你的循环：
  Sub Loop1（）
 Dim x As Integer： x = 1 
 
 Do Until x = Range（A8）+ 1 
 range（'你的单元格'）。Formula ='你的公式'
 
x = x + 1 
循环
 End Sub 
  
 
 
 
 
 
 
我猜想你想循环遍历每个月，并将该部分添加到公式中，然后将结果放在某个单元格中：
  Sub Loop1（）
 Dim x As Integer：x = 1 
 Dim formulaString As String 
 
直到x =范围（A8 ）+ 1 
如果formulaString =然后
 formulaString ==
 Else 
 formulaString = formulaString& +
 End If 
 
 formulaString = formulaString +SUMPRODUCT（SUMIFS（INDEX（'Program Data'！A：GA，0，MATCH（
 formulaString = formulaString& ;&&AC7&&（32）&& &&&&
 formulaString = formulaString&& B15&数据'！1：1,0）），
 formulaString = formulaString&'程序数据'！$ M：$ M，$ A2，'程序数据'！$ E：$ E，B2：B6 ）
 
x = x + 1 
循环
 
范围（C1）Value = formulaString 
 End Sub 
  
 
I have data that I need to create some sort of loop for. Basically, I'm searching for a column that has the info I need using a string search, then summing based on various criteria. My problem is that the data is separated by months (using a numberical designation; i.e; Jan=01, Feb=02, etc) and I need a way to add up all the info to date. The formula I am using is below:
=SUMPRODUCT(SUMIFS(INDEX('Program Data'!A:GA,0,MATCH("*"&AC7&" "&AC10&" -"&A8&" "&B15&"*",
  'Program Data'!1:1,0)),'Program Data'!$M:$M,$A2,'Program Data'!$E:$E,B2:B6))
Where A8 is the cell that contains the selected month of interest. So lets say A8=08 (August). I want to execute this formula for all previous values of A8 and add them together, adding upwards UNTIL A8 equals the current selection. (or while A8<09, however this can be accomplished)


So assuming A8=08, I want it to start at A8=01, run this code and find the value. Then run A8=02, find that value, then add it to the first value, continuing this until we get to A8=08 and then reporting the final value. Since the single-digit months are written in two-digits (ie; 01, 02, 03, etc) and searched using the leading 0, I think its fairly important the loop account for it up until we get two-digit months (10, 11, 12)....

Something along the lines of (and this is NOT code, just a general thought-process):
Sub Loop1()
Dim x as integer
x=01
Do until x = Cell A8
      (insert the formula above, but with "&A8&" = x)
x + 1 = x
End Sub
but even this thought-process doesn't seem to add the resulting formula values together, so I need help with that. I don't know how to perform do loops in excel, so any help you care to offer would be sincerely appreciated. I'm afraid I don't really know where to start, or the logical order each statement. Thanks for looking!

EDIT

Please excuse my poor description, hopefully this clarifies:

The code is a part of a table. Cell A8 is a drop-down selection where you choose the numberical month you want to examine (01, 02 --> 12). The formula then looks for a string using a set of reference cells, lets say "Rev Bud 08 -2013"(where 08 comes from your selection), then looks for a column with that title then performs the sumifs. But I want a year-to-date, so i want it to exceute the formula once for each x, starting at x=1, and up to and including whatever value is selected in cell A8, then add the results from all of the executions together and display that value in a cell of my choosing, let's say cell C50. So I want it to do the sumifs starting on the column with the title "Rev Bud 01 -2013" then "Rev Bud 02 -2013" .... all the way until "Rev Bud [my selection] -2013" and add all of those together. 
解决方案
I'm not quite following the question in terms of how the formula is supposed to change and what cell the final formula is supposed to reside in but here is how to structure your loop:
Sub Loop1()
    Dim x As Integer: x = 1

    Do Until x = Range("A8") + 1
        range("'your cell'").Formula = 'your formula'

        x = x + 1
    Loop
End Sub




I'm guessing you want to loop through each month and add that part to the formula and then put the result in some cell like this:
Sub Loop1()
    Dim x As Integer: x = 1
    Dim formulaString As String

    Do Until x = Range("A8") + 1
        If formulaString = "" Then
            formulaString = "="
        Else
            formulaString = formulaString & " + "
        End If

        formulaString = formulaString + "SUMPRODUCT(SUMIFS(INDEX('Program Data'!A:GA,0,MATCH("
        formulaString = formulaString & """*""" & "&AC7&""" & Chr(32) & """&AC10&""" & " -"""
        formulaString = formulaString & "&A" & x & "&"" """
        formulaString = formulaString & "&B15&" & """*""" & ",'Program Data'!1:1,0)),"
        formulaString = formulaString & "'Program Data'!$M:$M,$A2,'Program Data'!$E:$E,B2:B6))"

        x = x + 1
    Loop

    Range("C1").Value = formulaString
End Sub


                        
这篇关于麻烦创建一个excel Do循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！