CODEWITHSUNDEEP
excelvba
LeoEY
LeoEY

Reputation: 29

Selecting range based on specific text then fill with text

I trying to write a code where I want to look select range from Start to End in column A then fill the selected range in Column B with '1'. But my code only fill up bottom parts and missed out top parts. Refer to this photo. How do i make sure it go through every row and when it encounter Start, it will look for nearest End and fill up column B?

Table

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

Upvotes: 0

Views: 222

Answers (2)

Mech
Mech

Reputation: 4015

This will find Start, loop through until it finds End. Tested and working as requested.

Sub Select()
 Dim LastRowA, i As Long

    With ThisWorkbook.Worksheets("Sheet1")
        LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To LastRowA
            If .Cells(i, 1) = "Start" Then
                Do Until .Cells(i, 1) = "End"
                    .Cells(i, 2).Value = 1
                    i = i + 1
                Loop
                .Cells(i, 2).Value = 1
            End If
        Next i
    End With
End Sub

Upvotes: 1

Michael
Michael

Reputation: 4828

Find is a precarious beast to use in VBA. It would be far simpler to just loop through the cells, keeping track of whether or not you're between Start and End:

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

Upvotes: 1

Related Questions