Excel 在结束与循环和 for 循环之间崩溃?

Question

随机添加一行会使我的 excel 崩溃。

• 崩溃可能发生在end with或for rij = 4 to lastRow.

与在行

• 之间暂停 30 秒相比，通过行进行非常快的调试会使程序崩溃得更快。

• 我在不同的模块中有完全相同的代码，它确实在那里工作。

• 当我调试它时，最后一行的值为 5，因此无法Rij > lastRow

  问题

法典：

Option Explicit
Dim lastRow As Long
Dim IngevuldeCellen As Integer
Dim Ingevuld As Integer
Dim Rij As Integer
Dim tbl As Object
Dim AantalRijen As Integer
Dim laatsteRij As Long
Private Sub WorkSheet_Change(ByVal Target As Range)
'Als laatste rij 6 ingevulde velden heeft doe dit
lastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
IngevuldeCellen = WorksheetFunction.CountA(Range("B" & lastRow & ":G" & lastRow))
If IngevuldeCellen >= 4 Then
Call Module3.AddRowToBottom
Range("F1:F" & lastRow).Interior.Color = RGB(59, 148, 0)
Range("A1:A" & lastRow).Interior.Color = RGB(59, 148, 0)
ElseIf IngevuldeCellen < 4 Then
Range("A" & lastRow).Interior.Color = RGB(255, 0, 0)
Range("F" & lastRow).Interior.Color = RGB(255, 0, 0)
End If


Ingevuld = WorksheetFunction.CountA(Range("H6:O18"))
If Ingevuld >= 10 Then
With Sheets("Ruimtelijst")
lastRow = .Cells(Cells.Rows.Count, "G").End(xlUp).Row
End With
For Rij = 4 To lastRow
If Not Intersect(Target, Blad3.ListObjects("tbl_" & Rij - 3).Range) Is Nothing Then
'Zoek de laatste rij van de tabel op
Set tbl = Blad3.ListObjects("tbl_" & Rij - 3)
AantalRijen = tbl.Range.Rows.Count
laatsteRij = tbl.Range.Cells(AantalRijen, "E").Row
'Roept module
Call Module3.AddRow(laatsteRij)
End If
Next Rij
End If
End Sub

Answer 1

多亏了Teamothy，我发现VBA模块可能会随机损坏。我通过复制所有代码并将其粘贴到新的电子表格中来解决此问题。