vba - 如何将我的宏更改为 Worksheet_Change 事件 Excel VBA

Question

我有一个在工作簿关闭时调用的宏。它检查不同工作表上两个表中的列，并根据找到的内容分配行号。

Worksheet_Change处理程序位于带有 Projects 的工作表上范围。 Database范围位于同一工作簿中的另一个工作表上。

每当我在其他任何地方调用宏时，它要么会生成错误，要么会导致 excel 部分卡住的常见错误(任何人都知道这到底是什么？!？!？!)。

无论如何，我放弃之前的最后手段是将宏更改为工作表更改事件，我想知道是否可以获得一些帮助来创建它。
原始宏:

Sub FindRow()
'This module verifies row numbers in the database by matching them to the opportunities in the Projects
'worksheet. It then assigns row numbers in the Projects worksheet.

    Application.ScreenUpdating = False
    Dim LastRow As Long

    LastRow = Application.ThisWorkbook.Sheets("Projects").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Projects").Range("B2:B" & LastRow)
        Set foundRng = Sheets("Database").Range("C:C").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            rng.Offset(0, -1) = foundRng.Row
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

我建议的更改:

 Public Sub Worksheet_Change(ByVal Target As Range)
     Dim Records As Range

        Set Records = Range("Records")
        If Not Application.Intersect(Records, Range(Target.Address)) Is Nothing Then
        Application.ScreenUpdating = False
        Dim LastRow As Long    
        LastRow = Application.ThisWorkbook.Sheets("Projects").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        Dim rng As Range
        Dim foundRng As Range
        For Each rng In Sheets("Projects").Range("B2:B" & LastRow)
            Set foundRng = Sheets("Database").Range("C:C").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not foundRng Is Nothing Then
                rng.Offset(0, -1) = foundRng.Row
            End If
        Next rng
        Application.ScreenUpdating = True
End If
    End Sub

但是，我在定义变量 LastRow 的行上不断收到错误消息。 .
即使之前所有内容都已正确定义，我也会收到应用程序定义的错误。

提前致谢。

Answer 1

Worksheet_Change handles is located on the sheet with Projects range. Database range is located on another worksheet in the same workbook. – Remi 1 min ago

这意味着 rng也在 Projects床单:

For Each rng In Sheets("Projects").Range("B2:B" & LastRow)

(顺便说一句 Me.Range("B2:B" & LastRow) 在这里就不会那么模棱两可了)

您正在处理 Worksheet_Change Projects 上的事件工作表，每当 Projects 上的单元格值更改时，Excel 就会触发该工作表。床单。然后在那个处理程序中，你这样做:

rng.Offset(0, -1) = foundRng.Row

与 rng是 Projects 上的一个范围工作表，您正在进入工作表更改的递归循环，这可能是您的代码崩溃的原因。

当您在处理工作表更改时更改工作表时，您需要告诉 Excel“没关系，我知道了”，方法是防止它重新触发 Worksheet.Change。每次事件:

Application.EnableEvents = False

'...code...

Application.EnableEvents = True

此外，当您切换 Application.ScreenUpdating = False ，您是在告诉 Excel“在我这么说之前不要重新绘制自己”——在很多情况下，这可以大大加快速度，但这也意味着如果发生不好的事情，您需要手动将其重新打开。

您可以通过实现错误处理程序来避免这种情况 - 这是原则:

Sub DoSomething()
    On Error GoTo CleanFail
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    '...code...

CleanExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
CleanFail:
    Debug.Print Err.Description
    Stop
    Resume CleanExit
    Resume 'F8 takes you to the error-throwing statement
End Sub