时间戳

首页 > 解决方案 > 无法解决运行时错误 92：未初始化 For 循环。任何想法？

excel - 无法解决运行时错误 92：未初始化 For 循环。任何想法？

问题描述


如果满足某些条件，我有一个小循环来检查所有工作表和过去并复制一列。但是，在完成所有工作表后，我遇到了“运行时错误 92：未初始化 For 循环”。我了解，Next ws只要循环正在运行（直到最后一个工作表）或发生错误，就会得到解决。我不明白为什么Next ws在循环完成后使用。
你有什么想法我做错了吗？
提前谢谢了！

Dim MonthBC As String
Dim YearBC As String
Dim Phase As String
Dim colBC As Long
Dim colNo As Long
Dim vCol As Variant
Dim coli As Long

MonthBC = Form_Start_Update.ComboBox_Month.Value
YearBC = Form_Start_Update.ComboBox_Year.Value
Phase = "Plan"

For Each ws In ThisWorkbook.Worksheets
    With ws
        Debug.Print ws.Range("A1").Parent.Name
        colNo = ws.Cells(8, Columns.Count).End(xlToLeft).Column
        vCol = Application.WorksheetFunction.Transpose(ws.Range(Cells(8, 1).Address, Cells(10, colNo).Address).Value2)
        If colNo = 1 Then
        GoTo Continue_Next
        Else
            For coli = LBound(vCol, 1) To UBound(vCol, 1)
                On Error GoTo Continue_Next
                If IsDate(vCol(coli, 1)) = True Then
                    vCol(coli, 1) = Year(vCol(coli, 1))
                End If
                If vCol(coli, 1) = YearBC Then
                    If vCol(coli, 2) = MonthBC Then
                        If vCol(coli, 3) = Phase Then
                        colBC = coli
                        ws.Range(Cells(1, colBC + 1).Address).EntireColumn.Insert
                        ws.Range(Cells(1, colBC).Address, Cells(Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row, colBC).Address).Copy
                        ws.Range(Cells(1, colBC + 1).Address).PasteSpecial Paste:=xlPasteValues
                        ws.Range(Cells(11, 10).Address, Cells(Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row, 10).Address).Copy
                        ws.Range(Cells(11, colBC).Address).PasteSpecial Paste:=xlPasteFormulas
                        End If
                    End If
                End If
            Next coli
        End If
    End With

Continue_Next:
    Next ws

标签： excelvbaloopsonerror

解决方案

问题是在循环内使用标签。更改代码的逻辑并摆脱它们。仅供参考，当您转到错误标签时，您需要重置错误处理程序，但同样，您还是想摆脱标签。

/e：更准确地说，on error goto Continue_Next转到循环之外的标签，我相信这会导致错误。如果您在这里期待错误，On Error Resume Next请放在顶部，然后测试您的错误If Err.Number > 0 then Exit For- 不需要标签。记得清除错误和错误处理程序。尝试这样的事情：

For Each ws In ThisWorkbook.Worksheets

    Debug.Print ws.Range("A1").Parent.Name
    colNo = ws.Cells(8, Columns.count).End(xlToLeft).Column
    vCol = Application.WorksheetFunction.Transpose(ws.Range(Cells(8, 1).Address, Cells(10, colNo).Address).Value2)

    If colNo > 1 Then
        For coli = LBound(vCol, 1) To UBound(vCol, 1)
            On Error Resume Next
            'test your error
            If Err.number > 0 Then Exit For
            On Error GoTo 0
            If IsDate(vCol(coli, 1)) = True Then vCol(coli, 1) = Year(vCol(coli, 1))
            If vCol(coli, 1) = YearBC Then
                If vCol(coli, 2) = MonthBC Then
                    If vCol(coli, 3) = Phase Then
                        colBC = coli
                        ws.Range(Cells(1, colBC + 1).Address).EntireColumn.Insert
                        ws.Range(Cells(1, colBC).Address, Cells(Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).row, colBC).Address).Copy
                        ws.Range(Cells(1, colBC + 1).Address).PasteSpecial Paste:=xlPasteValues
                        ws.Range(Cells(11, 10).Address, Cells(Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).row, 10).Address).Copy
                        ws.Range(Cells(11, colBC).Address).PasteSpecial Paste:=xlPasteFormulas
                    End If
                End If
            End If
        Next coli
        On Error GoTo 0
    End If
Next ws

推荐阅读