Code with compile error: loop without do. But Do is in the code what is wrong?

Question

I keep getting a compiling error: Loop without Do even though I wrote Do in my code. What am I missing?

Sub SearchForString()

    Dim StartCell As Integer
    Dim EndCell As Integer

    StartCell = 2
    EndCell = 545

    Do While StartCell < EndCell

    Sheets("tian_corp_donations_Aug2019_how").Activate
    Sheets("tian_corp_donations_Aug2019_how").Select

    With ThisWorkbook.Worksheets("tian_corp_donations_Aug2019_how")

        'If value in column E = "Company_Name", copy entire row to Sheet2
        If Range("B:StartCell").Value = "George Weston Limited" Then
        'Select row in Sheet1 to copy
            Rows(StartCell).Select
            Selection.Copy
        'Paste row into Sheet2 in next row
            Sheets("Sheet2").Select
            Rows(StartCell).Select
            ActiveSheet.Paste
        'Move counter to next row
        'LCopyToRow = LCopyToRow + 1
            Exit Do                
        End If

        'Go back to Sheet1 to continue searching
        Sheets("tian_corp_donations_Aug2019_how").Select

        StartCell = StartCell + 1       

    Loop                

End Sub

You're missing an `End With`. But you actually are never referring to the `With`. Also `"B:StartCell"` is problematic. Variables should be outside quotes. All of this could be simplified by just filtering though and then copying visible cells, instead of looping. — BigBen, Sep 12 '19 at 20:21
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Also your row counting variables must be `Long`. Excel has more rows than `Integer` can handle. — Pᴇʜ, Sep 13 '19 at 06:04

score 0 · Answer 1 · answered Sep 12 '19 at 21:14

If I understand what your desired output is, I'm not sure a Do Loop is the best option. Maybe a For Each would work better. Try This:

Sub SearchForString()

    Dim StartCell As Integer
    Dim EndCell As Integer

    StartCell = 2
    EndCell = 545

    Sheets("tian_corp_donations_Aug2019_how").Activate
    Sheets("tian_corp_donations_Aug2019_how").Select

    Dim Rng As Range
    Set Rng = Range("E2:E545")

    Dim cell As Range
        For Each cell In Rng
        'If value in column E = "Company_Name", copy entire row to Sheet2
            If Cells(StartCell, "E").Text = "George Weston Limited" Then
            'Select row in Sheet1 to copy
                Rows(StartCell).Select
                Selection.Copy
            'Paste row into Sheet2 in next row
                Sheets("Sheet2").Select
                Rows(StartCell).Select
                ActiveSheet.Paste
            'Move counter to next row
            'LCopyToRow = LCopyToRow + 1
            Else
            End If        

        'Go back to Sheet1 to continue searching
        Sheets("tian_corp_donations_Aug2019_how").Select

        StartCell = StartCell + 1

        Next cell   

End Sub

Code with compile error: loop without do. But Do is in the code what is wrong?

1 Answers1