vba - Excel VBA反向for循环步进超出最终值

Question

我正在调试下面的代码。当我循环遍历它时，我注意到 row_j 的值为 1，尽管 For row_j = LastRow_date_new To 2 Step -1。

我希望 row_j 的最小值为 2，因为第 1 行中没有数据。SearchCol 中的值是格式为 20/01 的日期/2015 09:15:00，没有间隙或空值。

row_j 用于设置 End_row，它被传递到 Copy_to_b 并在那里传播错误。

谁能看出我的问题出在哪里？

此外，您能否推荐一种在满足与 Date_end 匹配的所需值时退出循环的方法？

谢谢

Sub select_date_range(LastCol As Long, LastRow_date_new As Long, DateMax As Date, Date_end As Date)
Dim SearchCol As Integer
Dim row_i As Integer
Dim row_j As Integer
Dim Start_row As Integer
Dim End_row As Integer

With Worksheets("a")
    For SearchCol = 1 To LastCol Step 3
        LastRow_date_new = Application.CountA(.Range((.Cells(1, SearchCol)), (.Cells(65536, SearchCol))))
        For row_i = 2 To LastRow_date_new
            If Sheets("a").Cells(row_i, SearchCol).Value = DateMax Then Start_row = row_i
        Next row_i
        For row_j = LastRow_date_new To 2 Step -1
            If Sheets("a").Cells(row_j, SearchCol).Value = Date_end Then End_row = row_j
        Next row_j
        ''''''' use range col1, row i to col2, row j to copy into new sheet
        Call copy_to_b(Start_row, SearchCol, End_row)
    Next SearchCol
End With

End Sub

Answer 1

随着 For row_j = LastRow_date_new To 2 Step -1，并且在每个 For 之后，您的变量将等于最后一个值 + 步骤，这就是 For-循环自行退出。

因此，如果您不想将该变量设置为 1，则必须在 For 循环之后将其设置为正确的值，如下所示:

    For row_i = 2 To LastRow_date_new
        If Sheets("a").Cells(row_i, SearchCol).Value = DateMax Then Start_row = row_i
    Next row_i
    row_i=2
    For row_j = LastRow_date_new To 2 Step -1
        If Sheets("a").Cells(row_j, SearchCol).Value = Date_end Then End_row = row_j
    Next row_j
    row_j=2

要在匹配条件时退出循环，可以使用 Exit For，有些人会说它不优雅，但它确实有效(以及下面代码审查的另一种方法)

Sub select_date_range(LastCol As Long, LastRow_date_new As Long, DateMax As Date, Date_end As Date)
Dim SearchCol As Integer
Dim row_i As Integer
Dim row_j As Integer
Dim Start_row As Integer
Dim End_row As Integer

With Worksheets("a")
    For SearchCol = 1 To LastCol Step 3
        LastRow_date_new = Application.CountA(.Range((.Cells(1, SearchCol)), (.Cells(65536, SearchCol))))

        For row_i = 2 To LastRow_date_new
            If CDate(.Cells(row_i, SearchCol).Value) <> CDate(DateMax) Then
            Else
                Start_row = row_i
                Exit For
            End If
        Next row_i

        For row_j = LastRow_date_new To 2 Step -1
            If CDate(.Cells(row_j, SearchCol).Value) <> CDate(Date_end) Then
            Else
                End_row = row_j
                Exit For
            End If
        Next row_j


        ''''''' use range col1, row i to col2, row j to copy into new sheet
        Call copy_to_b(Start_row, SearchCol, End_row)
    Next SearchCol
End With

End Sub

要替换 For，您可以使用 Do While 或 Do Until :

row_j = LastRow_date_new
Do While row_j >= 2 And .Cells(row_j, SearchCol).Value <> Date_end
    row_j = row_j - 1
Loop
End_row = row_j