退出Excel中的嵌套循环 [英] exit a nested loop in excel

问题描述

我有一个循环在一个宏写我的以下结构：

i have a loop in a macro i'm writing of the following structure:

这本书有两个工作表。原始数据（因此是endpointData变量）和G / L（总帐）表（因此是endpointGL变量）

there are two worksheets in this book. raw data (hence the endpointData variable) and a G/L (General ledger) sheet (hence the endpointGL variable)

有三个 / code>循环：

there are three for loops in this function:

（1）第一个循环遍历原始数据文件中的每个记录。
（2）第二个循环遍历经过验证的匹配从 REGEXP （正则表达式搜索）和
（3）第三个循环遍历G / L并找到相应的匹配，并将数据放入适当的位置（使用： PUT_DATA_RANGE ）。

(1) the first loop iterates through each record in the raw data file.
(2) the second loop iterates through the verified matches from REGEXP (a regular expression search) and
(3) the third loop goes through the G/L and finds a corresponding match and puts (using: PUT_DATA_RANGE) that data into the appropriate spot.

这是我得到的一些：

psuedocode：

psuedocode:

For i = 2 To endpointData

    If SOME_TEST = True Then
        Set MATCHES = REGEXP.EXECUTE()
        For Each myMatch In MATCHES
            For j = 1 To endpointGL
                If myMatch.value = SOME_CONDITION Then
                    PUT_DATA_RANGE
                    Exit For
                ElseIf myMatch.value <> SOME_CONDITION Then
                    MsgBox ("there might be a problem")
                    ' EXIT BOTH LOOPS HERE

                    ' write handler code
            Next
            End If
        Next
    End If
Next i

现在你会注意到我对自己有几点评价。如果第三个循环在G / L中找不到匹配，则代码当前中断以通知用户。但是消息框 MsgBox（可能有问题）与第三个循环一起循环。如何让excel退出BOTH循环，并将FIRST for循环引入原始数据中的下一个可用记录？

now you'll notice that i have a few comments to myself. if the third loops finds no match in the G/L the code currently interrupts to notify the user. but that message box MsgBox("there might be a problem") is looped through along with the third loop. how do i get excel to exit BOTH loops and bring the FIRST for loop to the next availabe record in the raw data?

顺便说一句，我已经尝试退出了一个 Exit For ，但似乎不起作用究竟。

by the way, i've tried exiting it with an Exit For but that doesn't seem to work exactly.

推荐答案

我看到两个选项。

1）的布尔值来检查外部循环是否应该继续

1) Use a series of booleans to check if the outer loops should continue

2）使用goto语句从内部循环中直接返回到主程序

2) Use a goto statement to exit from the inner loop directly back to the main procedure

在这种情况下，goto可能不会那么糟糕，因为你没有跳到代码完全不同的部分。只需记录好...

In this situation, the goto might not be so bad since you aren't jumping to a completely different part of the code. Just document it well...

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