excel - 找不到变量时如何处理

Question

我编写了一些代码，目的是在工作表“main”上循环遍历行，从 C 列（Reg）中的单元格中获取值，然后根据相应的开始字母移动到相关选项卡B列中的单元格。但是有时（Reg）在任何其他选项卡上都不存在，当它在任何其他相应的选项卡中找不到（reg）时，我无法弄清楚如何处理它。

我尝试使用 on error goto next ，但是当它从我不打算让它查看的单元格中拉回值时，最终只会导致问题进一步进入循环。我的 VB 真的很基础，所以我不确定如何更好地处理这个问题，因为我的代码中有很多 .select 并且我被告知我不应该使用它。

Sub comments_chase()

Dim comment, chase As String
Dim Reg As String
Dim reg_add As String
Dim Foundrange As String



Application.ScreenUpdating = False



    Workbooks("Progression chases.xlsm").Activate

    Worksheets("main").Activate



    Range("C4").Select



    Do Until ActiveCell.Value = ""

    reg_add = ActiveCell.Address

    Reg = ActiveCell.Value

    ActiveCell.Offset(0, -1).Select



    On Error GoTo Error_step



    If Left(ActiveCell.Value, 1) = "L" Or Left(ActiveCell.Value, 1) = "M" Or Left(ActiveCell.Value, 1) = "N" Or Left(ActiveCell.Value, 1) = "O" Or Left(ActiveCell.Value, 1) = "P" Or Left(ActiveCell.Value, 1) = "Q" Then

    Worksheets("Adie").Activate

    Set Foundrange = Range("C:C").Find(Reg, LookAt:=xlWhole).Select

        If Foundrange Is Nothing Then

        GoTo Error_step

        Else

    ActiveCell.Offset(0, 11).Select

    comment = ActiveCell.Value

    ActiveCell.Offset(0, 1).Select

    chase = ActiveCell.Value

    Workbooks("Progression chases.xlsm").Activate

    Worksheets("main").Activate

    Range("C:C").Find(Reg, LookAt:=xlWhole).Select

    ActiveCell.Offset(0, 11).Select

    ActiveCell.Value = comment

    ActiveCell.Offset(0, 1).Select

    ActiveCell.Value = chase

    End If



    On Error GoTo Error_step



        ElseIf Left(ActiveCell.Value, 1) = "A" Or Left(ActiveCell.Value, 1) = "B" Or Left(ActiveCell.Value, 1) = "C" Then

        Worksheets("Andy").Activate

    Set Foundrange = Range("C:C").Find(Reg, LookAt:=xlWhole).Select

    If Foundrange Is Nothing Then

    GoTo Error_step

    Else

        ActiveCell.Offset(0, 11).Select

        comment = ActiveCell.Value

        ActiveCell.Offset(0, 1).Select

        chase = ActiveCell.Value

        Workbooks("Progression chases.xlsm").Activate

        Worksheets("main").Activate

        Range("C:C").Find(Reg, LookAt:=xlWhole).Select

        ActiveCell.Offset(0, 11).Select

        ActiveCell.Value = comment

        ActiveCell.Offset(0, 1).Select

        ActiveCell.Value = chase

        End If



On Error GoTo Error_step

            ElseIf Left(ActiveCell.Value, 1) = "D" Or Left(ActiveCell.Value, 1) = "E" Or Left(ActiveCell.Value, 1) = "F" Or Left(ActiveCell.Value, 1) = "G" Or Left(ActiveCell.Value, 1) = "H" Or Left(ActiveCell.Value, 1) = "J" Or Left(ActiveCell.Value, 1) = "K" Then

            Worksheets("Georgia").Activate

    Set Foundrange = Range("C:C").Find(Reg, LookAt:=xlWhole).Select

    If Foundrange Is Nothing Then

    GoTo Error_step

    Else

            ActiveCell.Offset(0, 11).Select

            comment = ActiveCell.Value

            ActiveCell.Offset(0, 1).Select

            chase = ActiveCell.Value

            Workbooks("Progression chases.xlsm").Activate

            Worksheets("main").Activate

            Range("C:C").Find(Reg, LookAt:=xlWhole).Select

            ActiveCell.Offset(0, 11).Select

            ActiveCell.Value = comment

            ActiveCell.Offset(0, 1).Select

            ActiveCell.Value = chase

            End If



On Error GoTo Error_step

                ElseIf Left(ActiveCell.Value, 1) = "R" Or Left(ActiveCell.Value, 1) = "S" Or Left(ActiveCell.Value, 1) = "T" Or Left(ActiveCell.Value, 1) = "U" Or Left(ActiveCell.Value, 1) = "V" Or Left(ActiveCell.Value, 1) = "W" Or Left(ActiveCell.Value, 1) = "(" Or Left(ActiveCell.Value, 1) = "[" Or Left(ActiveCell.Value, 1) = "*" Then

                Worksheets("Leona").Activate

    Range("C:C").Find(Reg, LookAt:=xlWhole).Select

    Foundrange = ActiveCell.Value

    If Foundrange Is Nothing Then

    GoTo Error_step

    Else

                ActiveCell.Offset(0, 11).Select

                comment = ActiveCell.Value

                ActiveCell.Offset(0, 1).Select

                chase = ActiveCell.Value

                Workbooks("Progression chases.xlsm").Activate

                Worksheets("main").Activate

                Range("C:C").Find(Reg, LookAt:=xlWhole).Select

                ActiveCell.Offset(0, 11).Select

                ActiveCell.Value = comment

                ActiveCell.Offset(0, 1).Select

                ActiveCell.Value = chase



            End If

Error_step:  Worksheets("main").Activate



            Worksheets("main").Activate

           Range(reg_add).Select

           ActiveCell.Offset(1, 0).Select

           End If



    Loop



End Sub

上面的代码有 2 次稍微不同的尝试来处理它。展示我尝试过的东西

我收到运行时错误 424 Object required 并且在调试时它在块引用表（“Leona”）中的“If Foundrange is Nothing Then”处停止

理想情况下，如果一切正常，我希望它在工作表（“Leona”）上找到（Reg）并带回 Comment 和 Chase 中的值，然后将它们放入工作表（主）上的相关单元格中

谁能帮忙解决我哪里出错了？

标签： excelvbafind