vba VBA嵌套循环提前退出

VBA nested loops exiting early

提问by TornHair

I have a vba script which is supposed to copy data from one sheet to another. It does by means of three nested for loops. Stepping through the code in debugging these appear to work perfectly, but when the vba script is run they appear to stop too early. Otherwise the vba script works.

我有一个 vba 脚本，它应该将数据从一张纸复制到另一张纸。它通过三个嵌套的 for 循环来完成。在调试中单步调试这些代码似乎工作得很好，但是当 vba 脚本运行时，它们似乎停止得太早了。否则 vba 脚本工作。

I have been staring at this for hours and cannot for the life of me see what would cause the loops to stop early. I'm hoping the solution is something simple I've missed, but I am at a genuine loss, not for the first time since I started this.

我已经盯着这个看了几个小时，但我终其一生都看不到什么会导致循环提前停止。我希望解决方案是我错过的一些简单的东西，但我真的很茫然，这不是我开始这样做以来的第一次。

The sheet is organised as follows:

该表的组织如下：

Sheet1, contains the data to be copied.

Sheet1，包含要复制的数据。

• Each row contains a seperate response, of which there are 55 in the test data
• The sheet contains nine blocks of data, named Episode 1-9. Each episode contains column where an integer represent a start, end and interval time.
• In the test data each episode is identical except for the start/end times.
• The maximum value for EndTime is 36
• The test data is over the first four Episode blocks only, so Episode4 contains EndTime=36 for each row

• 每行包含一个单独的响应，其中测试数据中有 55 个
• 该工作表包含九个数据块，名为第 1-9 集。每集包含一列，其中一个整数表示开始、结束和间隔时间。
• 在测试数据中，除了开始/结束时间之外，每一集都是相同的。
• EndTime 的最大值为 36
• 测试数据仅在前四个情节块上，因此情节 4 每行包含 EndTime=36

Sheet2, where the data is to go -First column contains each RespondentID copied over 36 rows -Second column contains numbers 1-36, thus representing that time slot for that respondent -11 Columns after that contain the area where the data copied from sheet1 for that Respondent/Time is put. These 36x11 areas are named "Response1-55" in the test data

Sheet2，数据要去的地方 -第一列包含复制到 36 行的每个响应者 ID -第二列包含数字 1-36，从而代表该响应者的时间段 -11 之后的列包含从 Sheet1 复制数据的区域回答者/时间被放置。这些 36x11 的区域在测试数据中被命名为“Response1-55”

The logic of the vba script is as follows:

vba脚本的逻辑如下：

Counters: - n counter for number of respondents - r counter for number of episodes - i counter for rows within the responses being copied to.

计数器： - n 用于响应人数的计数器 - r 用于剧集数的计数器 - i 用于复制响应中的行。

->For each response (starting with n=1 to Respondents)
--> Select the first episode (Starting with r=1 to 9)
--->For each episode
--->Read the start, end and interval times
--->Starting from i = Start to i=End copy the relevant cells from the n'th row of the r'th episode
--->Copy those cells to the i'th row of the current response on sheet2
--->When you reach the EndTime of the current episode, go to the next one (next r)
-->If the episode you just finished has 36 as its EndTime then go to the next response, or continue till you run out of episodes.
->Next Response

->对于每个响应（从 n=1 开始到响应者）
--> 选择第一集（从 r=1 到 9 开始）
--->对于每一集
--->阅读开始、结束和间隔时间
- --> 从 i = Start 到 i=End 从第 r 集的第 n 行复制相关单元格
---> 将这些单元格复制到 sheet2 上当前响应的第 i 行
--- >当您到达当前剧集的 EndTime 时，转到下一个 (next r)
--> 如果您刚刚完成的剧集的 EndTime 为 36，则转到下一个响应，或者继续直到您用完剧集。
->下一个响应

In debugging the code appears to do exactly this.

在调试中，代码似乎就是这样做的。

However when I run the vba script on the test sheet it works only for episodes 1 and 2. The data from episodes 3 and 4 is not copied. Nothing is copied in its place, and the data which IS copied is correct in every respect. There are no error messages at any point.

但是，当我在测试表上运行 vba 脚本时，它仅适用于第 1 集和第 2 集。第 3 集和第 4 集的数据不会被复制。在它的位置上没有任何东西被复制，并且被复制的数据在各方面都是正确的。任何时候都没有错误消息。

If anyone could suggest why this might be happening I would build unto them an actual church. The answer could also be added here: https://stackoverflow.com/questions/119323/nested-for-loops-in-different-languagesWhich does not yet have a section for VBA.

如果有人能提出为什么会发生这种情况，我会为他们建造一座真正的教堂。也可以在此处添加答案：https: //stackoverflow.com/questions/119323/nested-for-loops-in-different-languages其中还没有 VBA 部分。

A link to the test sheet is here: http://dl.dropbox.com/u/41041934/MrExcelExample/TornHairExampleSheet.xlsm

测试表的链接在这里：http: //dl.dropbox.com/u/41041934/MrExcelExample/TornHairExampleSheet.xlsm

The relevant part of the code is here

代码的相关部分在这里

Sub PopulateMedia()
    Application.ScreenUpdating = False

    'Count the total number of response rows in original sheet
    Dim Responses As Long, n As Integer, i As Integer, r As Integer
        Responses = (Sheets("Sheet1").UsedRange.Rows.Count - 3) ' equals 55 in test sheet

    'For each response...
    For n = 1 To Responses
        i = 1 'Reset i for new response
            Dim curr_resp As Range
                Set curr_resp = Sheets(2).Range("Response" & n) 'Define a range containing all response data

            For r = 1 To 9  'For each episode...
                Dim curr_ep As Range 'Define a range containing episode data for all responses
                    Set curr_ep = Sheets(1).Range("episode" & r)

                Dim Stime As Integer, Etime As Integer, Itime As Integer 'Variables contain start, end and inter-episode times
                    Stime = curr_ep.Cells(n, 1)
                    Etime = curr_ep.Cells(n, 17)
                    Itime = curr_ep.Cells(n, 19)

                    For i = Stime To (Etime + Itime) 'for each time-slot...
                        If i <= Etime Then
                          Dim a As Variant
                            a = curr_ep.Range(curr_ep.Cells(n - 3, 1), curr_ep.Cells(n - 3, 11))
                            curr_resp.Rows(i) = a 'Copy data from above current episode to current response for slots between Stime and Etime
                        End If
                    Next i
                If Etime = 36 Then Exit For
             Next r
     Next n

    Application.ScreenUpdating = True
End Sub

To disclose, I have already had help on this project from this site, VBA copy from a union of two ranges to a row of another rangebut the code has been changed slightly since then and this is a different problem.

透露一下，我已经从这个站点获得了这个项目的帮助，VBA 从两个范围的联合复制到另一个范围的一行，但从那时起代码略有变化，这是一个不同的问题。

Once more, thank you enormously for any help which might come of this. I have been staring at this for hours and do not see where the error is. Any guidance at all greatly appreciated.

再一次，非常感谢您提供的任何帮助。我一直盯着这个几个小时，没有看到错误在哪里。任何指导都非常感谢。

回答by brettdj

I would post this as a comment if I could but this is too long. So here it is as a query /potential solution

如果可以的话，我会将此作为评论发布，但这太长了。所以这里是一个查询/潜在解决方案

I think your range references are the issue

我认为您的范围参考是问题所在

The code below is a cut-down version of your code

下面的代码是您的代码的简化版本

curr_epis a named range of episode1. It has a range address of $Y$4:$AQ$58

curr_ep是episode1的命名范围。它有一个范围地址$Y$4:$AQ$58

When you loop through the avariant you are setting a range with this syntax
a = curr_ep.Range(curr_ep.Cells(n - 3, 1), curr_ep.Cells(n - 3, 11))
which is equivalent to a = curr_ep.Range("Y2:AQ2")

当您遍历a变体时，您正在使用此语法设置一个范围，
a = curr_ep.Range(curr_ep.Cells(n - 3, 1), curr_ep.Cells(n - 3, 11))
相当于 a = curr_ep.Range("Y2:AQ2")

which means you are actually looking at AW2:BG2not Y2:AQ2which is what I think you may have intended, i.e. you are building in an unintended offset

这意味着您实际上正在查看的AW2:BG2不是Y2:AQ2我认为您可能想要的内容，即您正在构建一个意外的偏移量

Sub PopulateMedia()
    n = 1
    r = 1
    Dim curr_ep As Range
    Dim curr_test As Range
    Set curr_ep = Sheets(1).Range("episode" & r)
    Set curr_test = curr_ep.Range(curr_ep.Cells(n - 3, 1), curr_ep.Cells(n - 3, 11))
End Sub