CODEWITHSUNDEEP
vbaexcel
Frase
Frase

Reputation: 1

Loop Error (Next without For)

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

Upvotes: 0

Views: 77

Answers (2)

Divin3
Divin3

Reputation: 548

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

Upvotes: 0

Tarik
Tarik

Reputation: 11209

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

Upvotes: 2

Related Questions