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 stackoverflow.com/questions/5895908/continue-for-loop — Peter Schneider
– Peter Schneider, Commented May 16, 2015 at 6:15

Tarik · Accepted Answer · 2015-05-16 06:27:45Z

2

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

answered May 16, 2015 at 6:27

Tarik

11.2k2 gold badges31 silver badges47 bronze badges

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

Comments

Lrnd · Accepted Answer · 2015-05-16 06:33:16Z

0

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

answered May 16, 2015 at 6:33

Lrnd

5485 gold badges13 silver badges29 bronze badges

Collectives™ on Stack Overflow

Loop Error (Next without For)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related