Excel VBA Next without For error

Question

I am getting a "Next Without for Error on the following code:

sub test()
  numRows = 11
  For i = 0 To numRows
    If Cells(i + 1, 2) >= 0 Then
        Range((Cells(A, i + 1)), Cells(B, i + 1)).Select
        Selection.Copy
        Sheets("PasteLocation").Activate
        Range("Ai+1").Select
        Selection.Paste
  Next
End Sub

I assume that my "If" statement doesnt know that it's done and that the "Next" command thinks there should be another nested "for" in the "if" statement but I do not know where.

You should close your If with End If. If then Else Excel VBA - "End If" needed? — 001
– 001, Commented Jan 24, 2018 at 18:51
"I assume that my 'If' statement doesn't know that it's done" - precisely: you begin an If block but never terminate it - hence the compiler "thinks" Next is part of the If block, can't find a corresponding For statement, and blows up saying exactly that: there's a Next statement that shouldn't be there. All block syntax work the same way, and they can't be intertwined (i.e. you can't start a For loop, begin an If block, then close the For loop with a Next, and then somewhere further down close the If block with End If) — Mathieu Guindon
– Mathieu Guindon, Commented Jan 24, 2018 at 19:45
There should be questions that result interesting and useful for this community and this could be avoided by checking the code 2 more seconds — Moreno
– Moreno, Commented Jan 24, 2018 at 20:23

BruceWayne · Accepted Answer · 2018-01-24 18:58:48Z

3

As commented, you need End IF, but I also made some slight improvements:

Sub test()
numRows = 11
For i = 0 To numRows
    If Cells(i + 1, 2) >= 0 Then
        With Sheets("Sheet1")
            .Range(.Cells(1, i + 1), .Cells(2, i + 1)).Copy
        End With
        Sheets("PasteLocation").Range("A" & i + 1).Paste
    End If
Next
End Sub

Note: Change Sheets("Sheet1") to the appropriate sheet name.

answered Jan 24, 2018 at 18:58

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

obizues · Accepted Answer · 2018-02-05 18:24:26Z

0

The answer is in the first comment under my original question by user Johnny Mopp

You should close your If with End If. If then Else Excel VBA - "End If" needed?

answered Feb 5, 2018 at 18:24

obizues

1,4855 gold badges17 silver badges31 bronze badges

Collectives™ on Stack Overflow

Excel VBA Next without For error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related