VBA Next without For compile error

[PB7]

VBA colleagues:

I've got this VBA compile error: Next without For, when my code seems ok, though I realize some nuance could be missing or wrong.

Here's the code getting the error message:

For i = 5 To nlines
Range("A" & i).Select
If TestDates(ActiveCell.Value, "04/30/2011") = "1" Then
ActiveCell.Offset(0, 13).Value = ActiveCell.Offset(0, 10).Value
ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
Else
If TestDates(ActiveCell.Value, "04/30/2011") = "0" Then
ActiveCell.Offset(0, 12).Value = ActiveCell.Offset(0, 10).Value
ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
End If
Next i

Above, TestDates is a function doing date differential calculations on 2 different dates, returning the difference stated as a month (0,1,2,3 etc)
The TestDates function works fine in another module that calls it.
Thank you in advance for any guidance, feedback.

 

[Gary McMaster]

Extra "If".

Gary

For i = 5 To nlines
        Range("A" & i).Select
            If TestDates(ActiveCell.Value, "04/30/2011") = "1" Then
                ActiveCell.Offset(0, 13).Value = ActiveCell.Offset(0, 10).Value
                ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
            Else
            [B][COLOR=Red]If[/COLOR][/B] TestDates(ActiveCell.Value, "04/30/2011") = "0" Then
                ActiveCell.Offset(0, 12).Value = ActiveCell.Offset(0, 10).Value
                ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
            End If
    Next i

 

[Jonmo1]

This error is so missleading...

You're actually missing an End If..

Indentation of code helps to see this type of error.

For i = 5 To nlines
    Range("A" & i).Select
    If TestDates(ActiveCell.Value, "04/30/2011") = "1" Then
        ActiveCell.Offset(0, 13).Value = ActiveCell.Offset(0, 10).Value
        ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
    Else
        If TestDates(ActiveCell.Value, "04/30/2011") = "0" Then
            ActiveCell.Offset(0, 12).Value = ActiveCell.Offset(0, 10).Value
            ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
        End If '<--This was missing
    End If
Next i

Hope that helps.

 

[VoG]

Maybe

For i = 5 To nlines
    Range("A" & i).Select
    If TestDates(ActiveCell.Value, "04/30/2011") = "1" Then
        ActiveCell.Offset(0, 13).Value = ActiveCell.Offset(0, 10).Value
        ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
    ElseIf TestDates(ActiveCell.Text = "04/30/2011") = "0" Then
        ActiveCell.Offset(0, 12).Value = ActiveCell.Offset(0, 10).Value
        ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
    End If
Next i

Note: you do not have to select a cell in order to test its properties.

 

[pboltonchina]

Try changing

For i = 5 To nlines
Range("A" & i).Select
If TestDates(ActiveCell.Value, "04/30/2011") = "1" Then
ActiveCell.Offset(0, 13).Value = ActiveCell.Offset(0, 10).Value
ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
Else
If TestDates(ActiveCell.Value, "04/30/2011") = "0" Then
ActiveCell.Offset(0, 12).Value = ActiveCell.Offset(0, 10).Value
ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
End If
Next i
[CODE]
to

For i = 5 To nlines
Range("A" & i).Select
If TestDates(ActiveCell.Value, "04/30/2011") = "1" Then
ActiveCell.Offset(0, 13).Value = ActiveCell.Offset(0, 10).Value
ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
Else
ActiveCell.Offset(0, 12).Value = ActiveCell.Offset(0, 10).Value
ActiveCell.Offset(0, 19).Value = ActiveCell.Offset(0, 10).Value
End If
Next i
[/CODE]

 

[njimack]

You've got 2 If's but only 1 End If. Also, yo don't need to select cells. Try the following

For i = 5 To nlines

    If TestDates(Cells(i, 1).Value, "04/30/2011") = "1" Then
        Cells(14, i).Value = Cells(i, 11).Value
        Cells(i, 20).Value = Cells(i, 11).Value
    End If

    If TestDates(Cells(i, 1).Value, "04/30/2011") = "0" Then
        Cells(i, 13).Value = Cells(i, 11).Value
        Cells(i, 20).Value = Cells(i, 11).Value
    End If
Next i

 

[PB7]

Awesome, quick responses! Trying these now, will confirm back.

 

[Jonmo1]

Just an observation...

Is 1 or 0 the only 2 possible results of the TestDates function?
If so, there is no need for the 2nd IF..
Because if the First IF TestDates(...) = "1" is FALSE, then it MUST be "0", no need to run TestDates again.

Again, IF 1 or 0 are the only possible results...

 

[PB7]

Jonmo / all,

Right, code was missing the second End If, and after adding, worked fine. Sure, the error message is definitely a curve ball to the unitiated.

The problem code was indented when I pasted in, but in future will have to check how to keep it indented in the thread.

Anyways, thanks everyone for the great help here! Alms for the VBA-poor.

 

[Jonmo1]

Use code tags...

[#code]
Your Code Here
See indents
[#/code]

Remove the # signs

makes it look like

     Your Code Here
          See indents