vba - 我的vba循环在我逐步执行时会拉回所有正确的数据，但是当我运行宏时，它不会

我试图建立一个循环，当符合条件时回送某些数据，然后将结果发布到我的“主”表中。

不幸的是，当您运行宏时，它不会拉回所有数据。

但是，我认为这很奇怪，当您逐步执行时会如此。

在代码中的任何时候都没有错误消息，并且如果您单步执行/仅运行宏，代码将贯穿整个过程。

我在下面发布了我的代码：

Sub Loop_Data()
    'BR stands for Blank Row
    Dim i As Integer, j As Integer, k As Integer, m As Integer, BRMAin As Integer, BRData As Integer, BRPhysNot As Integer, _
    SearchRange As Range, strID As String, ExtEnd As Integer, FindRow As Range

    BRMAin = Sheets("Main").Cells(Rows.Count, "W").End(xlUp).Row
    BRData = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
    BRPhysNot = Sheets("PhysNot").Cells(Rows.Count, "A").End(xlUp).Row

    Set SearchRange = Sheets("Data").Range("A3:A" & BRData)
    Sheets("CoData").Activate
    'assign j for number of rows (units) and i to start at 6 (column J) and end at 21

    For j = 2 To 48
        i = 35
        Do Until i = 52
                'criteria
            If Cells(j, i - 1).Interior.Color <> RGB(51, 51, 51) And Cells(j, i - 1) > 0 And Cells(j, i).Interior.Color = RGB(51, 51, 51) Then
                'find duration o
                m = 0
                Do While Cells(j, i + m).Interior.Color = RGB(51, 51, 51)
                    m = m + 1
                Loop
                'check that the flagged is definitely matching criteria
                If Cells(j, i + m) = 0 Then
                    'set string ID as the string of uni & period to find in the helper column of Data sheet
                    'set k as row which that occurs in
                    strID = Cells(1, i) & Cells(j, 3)
                    Set FindRow = SearchRange.Find(strID)
                    k = FindRow.Row
                    'Pull back data into main sheet
                    ThisWorkbook.Sheets("Main").Range("X" & BRMAin + 1) = Sheets("Data").Cells(k, 8)
                    ThisWorkbook.Sheets("Main").Range("V" & BRMAin + 1) = Sheets("Data").Cells(k, 4)
                    ThisWorkbook.Sheets("Main").Range("W" & BRMAin + 1) = Sheets("Data").Cells(k, 2)
                    ThisWorkbook.Sheets("Main").Range("Y" & BRMAin + 1) = m
                    ThisWorkbook.Sheets("Main").Range("Z" & BRMAin + 1) = Sheets("CoData").Cells(1, i)
                End If
            End If
            i = i + 1
        Loop
    Next j
End Sub

最佳答案

如果Wait或DoEvents不起作用，请使用

Set FindRow = SearchRange.Find(strID)
k = FindRow.Row

你可以去

k = 0
For Each SearchCell In SearchRange
    If SearchCell.Text = strID Then k = SearchCell.Row
Next