如果数据超过360行，则VBA代码提前离开循环

Question

下面的代码运行得很好，直到有超过360行需要循环。如果超过360行，则在底部循环之后不会运行任何内容。实际上，代码在下面指示的位置停止循环通过第361行上的顶部循环。

我将所有行变量从整型更改为长整型，并使用行号来缩小出现问题的行号。如果我的行数少于361行，代码就可以正常运行。

Option Explicit
'Process report variables
Dim ProcRowCount As Long
Dim Process As String
Dim ProcSID As String
Dim ProcStat As String
Dim ProcBeg As Date
Dim ScheRow As Long
Dim ProcRow As Long
Dim OffName As String
Dim DueDate As Date
Dim procserv As Integer
'event report variables
Dim SchEvent As String
Dim EventSID As String
Dim EventRow As Long
Dim Event2025 As String
Dim EventOut As String
Dim EventDate As Date
Dim Eventdate2 As Date
Dim EventDue As Date
Dim NameSID As String
Dim AttempDate As Date
Dim AttempDate2 As Date

Public Sub Update_Process()

With ThisWorkbook.Worksheets("Process")
ProcRowCount = Worksheets("Process").Cells(Rows.Count, "a").End(xlUp).Row
ProcRow = 1
DueDate = Date - 30

Worksheets("Dashboard").Range("ag5:ag500").ClearContents
Set Case_Status = Worksheets("Tables").ListObjects("Case_Status")
Set Events = Worksheets("Tables").ListObjects("Events")
Set Occurrence = Worksheets("Tables").ListObjects("Occurrence")


'checks for offender to be in an active status and that the process
'has been in status date for more than 30 days
Do While ProcRow <= ProcRowCount
ProcStat = Worksheets("Process").Cells(ProcRow, "f")

If ProcStat = "txt_supervision_code" Or ProcStat = "" Then
    On Error Resume Next
    ProcRow = ProcRow + 1

ElseIf ProcStat <> "txt_supervision_code" Then
    ProcBeg = Worksheets("Process").Cells(ProcRow, "m")
    Active = Application.WorksheetFunction.VLookup(ProcStat,             Case_Status.Range, 3, False)

    If Active = "No" Then
        ProcRow = ProcRow + 1
    ElseIf ProcBeg < DueDate Then
        ProcSID = Worksheets("Process").Cells(ProcRow, "B")
        Process = Worksheets("Process").Cells(ProcRow, "l")
        OffName = Worksheets("Process").Cells(ProcRow, "c")
        Call EventReview
        ProcRow = ProcRow + 1
    Else: ProcRow = ProcRow + 1
    End If
End If
Loop

End With

Worksheets("Dashboard").Range("BG4") = procserv

End Sub

Private Sub EventReview()
'Loop though 2025 to see if a case audit or offender contact was documented on the case
With ThisWorkbook.Worksheets("2025")
ScheRow = Worksheets("2025").Cells(Rows.Count, "a").End(xlUp).Row
EventRow = 2
EventSID = Worksheets("2025").Cells(EventRow, "a")


Do While EventRow <= ScheRow
    Event2025 = Worksheets("2025").Cells(EventRow, "J")
    EventOut = Worksheets("2025").Cells(EventRow, "Q")
    EventSID = Worksheets("2025").Cells(EventRow, "A")
    EventDue = Worksheets("2025").Cells(EventRow, "O")
    CaseReview = Application.WorksheetFunction.VLookup(Event2025, Events.Range, 3, False)  ***Once on row 361, the loop stops here and goes back to the public sub
    OffenderCon = Application.WorksheetFunction.VLookup(Event2025, Events.Range, 2, False)
    EventOccurred = Application.WorksheetFunction.VLookup(EventOut, Occurrence.Range, 6, False)
    If ProcSID = EventSID And EventOccurred = "Yes" And (CaseReview = "Yes" Or OffenderCon = "Yes") And _
            EventDue > DueDate Then
        EventDate = Worksheets("2025").Cells(EventRow, "o")
        If Event2025 = "Process Service" And Process = "A-Warr" Then
            procserv = procserv + 1
            End If
        If Eventdate2 = "12:00:00 AM" Or Eventdate2 < EventDate Then
            Eventdate2 = EventDate
            EventRow = EventRow + 1
        Else: EventRow = EventRow + 1
            End If
    Else: EventRow = EventRow + 1
        End If


Loop


NameSID = OffName & " " & ProcSID

'loop through pivot table, insert date in offset column

With ThisWorkbook.Worksheets("Dashboard")

Set pvt = Worksheets("Dashboard").PivotTables("ProcessPivot")
Set Rng = pvt.DataBodyRange

    For Each cell In Rng
        If cell = NameSID And Eventdate2 <> "12:00:00 AM" Then
            cell.Offset(0, 5) = Eventdate2
            Eventdate2 = "12:00:00 AM"
        ElseIf cell = NameSID Then
            cell.Offset(0, 5).Value = "Not Reviewed"
        End If

    Next cell

End With
End With

End Sub

预期的结果是返回一个日期，如果有符合条件的日期，或者“未审核”。上面的代码没有返回任何错误消息。如果超过360行，它就会在循环中间离开EventReview()子例程，而不是继续到第二个循环返回循环的结果。

Answer 1

我猜你想要改变这一切

ProcRowCount = Worksheets("Process").Cells(Rows.Count, "a").End(xlUp).Row

到这个

ProcRowCount = Worksheets("Process").UsedRange.Rows.Count

因为你真的想要最后一行，前者转到最后一行，然后点击end，这可能会一直到第一行。

和这家伙一样

ScheRow = Worksheets("2025").Cells(Rows.Count, "a").End(xlUp).Row

旁注:当你这样做的时候

With ThisWorkbook.Worksheets("Process")

正如您所做的，那么您可以(也应该)这样做

.UsedRange.Rows.Count