next without for in excel VBA

Question

I have both next and for and I am still getting this error message saying "next without for". Can someone please help me with this error message? What am i doing wrong here?

Sub CheckCOA()
    new_coa_detected = 0
    Sheets("CAO & other").Select

    With ActiveSheet
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        MsgBox "Ref Table Length = " & lr
    End With

    Sheets("Month1").Select

    With ActiveSheet
        lr1 = .Cells(.Rows.Count, "A").End(xlUp).Row
        MsgBox "Month 1 Length = " & lr1
    End With

    For l = 2 To lr1
        code = Cells(l, 1)
        Sheets("CAO & other").Select

        For m = 2 To lr
            If Cells(m, 1) = code Then
                GoTo existing_code
            End If
        Next m
new_code:
        'New code detected
        Sheets("NewCOAsDetected").Select

        With ActiveSheet
            lr_new_coa = .Cells(.Rows.Count, "A").End(xlUp).Row
            Cells(lr_new_coa, 1) = code
            new_coa_detected = 1
            Sheets("Month1").Select

existing_code:
        'No action for existing code. Check next code

    Next l

    If new_coa_detected = 1 Then
        MsgBox "New COA codes have been detected. A list of these COA codes is available under the 'NewCOAsDetected' tab."
    End If
End Sub

① Your last `With ActiveSheet` has no `End With` ② I highly recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique to all of your code. — Pᴇʜ, Jan 14 '19 at 12:27
Also if you consistently indented your code you might well have spotted that. — SJR, Jan 14 '19 at 12:32
Possible duplicate of [Next Without For Error VBA](https://stackoverflow.com/questions/19085096/next-without-for-error-vba) — Tedinoz, Jan 17 '19 at 03:12

Error 1004 · Answer 1 · 2019-01-14T13:02:32.367

i think you are missing "End With"

        With ActiveSheet
            lr_new_coa = .Cells(.Rows.Count, "A").End(xlUp).Row
            Cells(lr_new_coa, 1) = code
            new_coa_detected = 1
            Sheets("Month1").Select
        End With

Also i try to edit your code as @Pᴇʜ mention above:

    Option Explicit

Sub CheckCOA()

    Dim new_coa_detected As Long

    new_coa_detected = 0

    With ThisWorkbook.Worksheets("CAO & other")
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        MsgBox "Ref Table Length = " & lr
    End With

    With ThisWorkbook.Worksheets("Month1")
        lr1 = .Cells(.Rows.Count, "A").End(xlUp).Row
        MsgBox "Month 1 Length = " & lr1
    End With

    For l = 2 To lr1
        code = ThisWorkbook.Worksheets("Month1").Cells(l, 1)

        For m = 2 To lr
            If ThisWorkbook.Worksheets("CAO & other").Cells(m, 1) = code Then
                GoTo existing_code
            End If
        Next m

new_code:

        'New code detected
        With ThisWorkbook.Worksheets("NewCOAsDetected")
            lr_new_coa = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Cells(lr_new_coa, 1) = code
            new_coa_detected = 1
            Sheets("Month1").Select
        End With

existing_code:

        'No action for existing code. Check next code

    Next l

    If new_coa_detected = 1 Then
        MsgBox "New COA codes have been detected. A list of these COA codes is available under the 'NewCOAsDetected' tab."
    End If

End Sub

next without for in excel VBA

1 Answers1