Error with Nested loops in Excel VBA

Question

I am having problems with my code on Excel VBA. I am trying to write a nested loop. But I keep getting the compile error: Next without For ( the line with Next r)

Here's my code

Sub clear()
r = 2
i = 310

For c = 15 To 28

r = 2
i = 310

For r = 2 To 306

If Cells(r, c) > Cells(306, c) Then
If Cells(r, 4) <> Cells(i - 1, c) Then
[ Cells(i, c) = Cells(r, 4) i = i + 1]


r = r + 1
Next r

Cells(308, c) = i - 309

Next c

End Sub

Thanks in advance

try indenting your code, you might find that you have 2 x `If`s without `End If`, that might be the reason. Also, in the code you shared, you are not doing anything with the variable `I`, you can replace `I` with 310 (or declare it at the top as `Const`) — Shai Rado, Jun 19 '17 at 06:16

sancho.s ReinstateMonicaCellio · Accepted Answer · 2017-06-19T06:31:11.730

0

You are (at least) missing endifs. Please check if code posted below follows your intended task.

Plus:

It is always convenient to require explicit declaration of variables.
It is always convenient to not use Cells alone, see this answer.
Line r = r + 1 is strange. r is already incremented by looping.

Suggested amended code (I do not have a system at hand to test it).

Option Explicit

Sub clear()
    Dim r as Integer, i as Integer, c as Integer
    Dim ws As Worksheet
    Set ws = ActiveSheet   ' or whatever you want
    'r = 2
    i = 310
    For c = 15 To 28
        'r = 2
        'i = 310
        For r = 2 To 306
            If ( ws.Cells(r, c) > ws.Cells(306, c) ) Then
                If ( ws.Cells(r, 4) <> ws.Cells(i - 1, c) ) Then
                    [ ws.Cells(i, c) = ws.Cells(r, 4) i = i + 1]   ' check syntax
                endif
            endif
            r = r + 1    ' check this
        Next r
        ws.Cells(308, c) = i - 309
    Next c
End Sub

edited Jun 19 '17 at 06:31

answered Jun 19 '17 at 06:15

sancho.s ReinstateMonicaCellio

14,708
20
93
185

you have a `For r = 2 To 306` loop , and before letting it increment `Next r` you are keeping another `r = r + 1` ? – Shai Rado Jun 19 '17 at 06:20
@ShaiRado - Absolutely, I was commenting on that strange line. Even though in very rare cases one might want to do that, I do not think this is one such case. – sancho.s ReinstateMonicaCellio Jun 19 '17 at 06:34
@sancho.s how to change the focus to a different sheet. I wrote a code and i want to run it for sheet 3, but the code is running for sheet 2. – Franklin Varghese Jun 19 '17 at 08:18
@FranklinVarghese - `set ws = Worksheets("sheet 3")` or similar. – sancho.s ReinstateMonicaCellio Jun 19 '17 at 08:29

Error with Nested loops in Excel VBA

1 Answers1