excel - 根据特定文本选择范围，然后填充文本

Question

我试图编写一个代码，我想在 A 列中查看从 Start 到 End 的选择范围，然后用“1”填充 B 列中的选定范围。但是我的代码只填满了底部而错过了顶部。参考这张照片。我如何确保它遍历每一行，当它遇到 Start 时，它会寻找最近的 End 并填满 B 列？

Sub Select()
 Dim LastRowA As Long, i As Long
 Dim findrow As Long, findrow2 As Long

    With ThisWorkbook.Worksheets("Sheet1")
        LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To LastRowA
            findrow = Range("A:A").Find("Start", Range("A1")).Row
            findrow2 = Range("A:A").Find("End", Range("A" & findrow)).Row
            Range("A" & findrow & ":A" & findrow2).Offset(0, 1).Value = "1"
        Next i
    End With
End Sub

标签： excelvba

Answer 1

Find 是在 VBA 中使用的不稳定的野兽。循环遍历单元格会简单得多，跟踪您是否在开始和结束之间：

Sub Select()
    Dim LastRowA As Long, i As Long, b As Boolean

    With ThisWorkbook.Worksheets("Sheet1")
        LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To LastRowA
            If .Cells(i, 1).Value = "Start" Then
                b = True
                .Cells(i, 2).Value = 1
            ElseIf .Cells(i, 1).Value = "End" Then
                b = False
                .Cells(i, 2).Value = 1
            ElseIf b Then
                .Cells(i, 2).Value = 1
            End If
        Next i
    End With
End Sub