Loop Error (Next without For)

Question

I am a real VBA amature and would really appreciate some help.

I have written the below code to cycle through a column, look for a certain string (heading), if the string is found, search for numbers below the string and copy values to a list.
I am getting a 'Next without For' compile error.

Thanks in advance for any assistance.

Sub data()


For i = 0 To 1000

If Range("C1").Offset(i, 0) <> Range("G2") Then Next i Else


For j = 1 To 20

If Not IsNumeric(Range("C1").Offset(i, 0).Offset(j, 0)) Then Next j Else

Range("G1").End(xlDown).Offset(1, 0) = Range("C1").Offset(i, 0).Offset(j, 0).Value

Next j
Next i


End Sub

Possible duplicate of http://stackoverflow.com/questions/5895908/continue-for-loop — Peter Schneider, May 16 '15 at 06:15
[Also Checkout loop examples](http://www.xlorate.com/excel-vba-loops.html) — Davesexcel, May 16 '15 at 15:25

score 2 · Answer 1 · answered May 16 '15 at 06:27

You can change your code as follows:

    For i = 0 To 1000
        If Range("C1").Offset(i, 0) = Range("G2") Then
            For j = 1 To 20
                If IsNumeric(Range("C1").Offset(i, 0).Offset(j, 0)) Then
                   Range("G1").End(xlDown).Offset(1, 0) = Range("C1").Offset(i, 0).Offset(j, 0).Value
                EndIf
            Next j
        EndIf
    Next i
End Sub

score 0 · Answer 2 · answered May 16 '15 at 06:33

This just looks awful. Let me help.

Firs, try to use the If like this:

If *condition* then
    'what you want to do
else
    'in every other cases, what do you want to do
End if

And use For like:

For i = 1 To *number of times you want to loop*
    'what do you want to loop
Next i

If you put If inside For, You close the If first; if you put For inside If, you always use Next i before End if

for example:

For i = 1 To *number of times you want to loop*
    If *condition* then
        'what you want to do
    else
        'if the condition is not true, what do you want to do
    End if
Next i

Loop Error (Next without For)

2 Answers2