Compile Error: Next without For?

arsenalgkjono

New Member
Joined
Sep 13, 2011
Messages
1
Here is the code, for some reason I am getting a next without for error. What is wrong with it?

Sub MeanCEOAge()
'
' MeanCEOAge Macro
'
Dim TotalAge As Integer
Dim CEOcount As Integer
Dim i As Integer

TotalAge = 0
CEOcount = 0

For i = 2 To 51

If Cells(i, 5).Value = "CEO" Then
CEOcount = CEOcount + 1
TotalAge = TotalAge + Cells(i, 3).Value
If Cells(i, 5).Value = "CEO/President" Then
CEOcount = CEOcount + 1
TotalAge = TotalAge + Cells(i, 3).Value
If Cells(i, 5).Value = "CEO/Chairman" Then
CEOcount = CEOcount + 1
TotalAge = TotalAge + Cells(i, 3).Value
End If

Next i
Cells(11, 8) = CEOcount
Cells(11, 7) = TotalAge

'
End Sub
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,722
Office Version
2010
Platform
Windows
Look at your code when it's indented. Each If should align with an End If, each For with a Next, each With with an End With, each Select with an End Select:

Code:
Sub MeanCEOAge()
    '
    ' MeanCEOAge Macro
    '
    Dim TotalAge    As Integer
    Dim CEOcount    As Integer
    Dim i           As Integer
    TotalAge = 0
    CEOcount = 0
 
    For i = 2 To 51
        If Cells(i, 5).Value = "CEO" Then
            CEOcount = CEOcount + 1
            TotalAge = TotalAge + Cells(i, 3).Value
 
            If Cells(i, 5).Value = "CEO/President" Then
                CEOcount = CEOcount + 1
                TotalAge = TotalAge + Cells(i, 3).Value
                If Cells(i, 5).Value = "CEO/Chairman" Then
                    CEOcount = CEOcount + 1
                    TotalAge = TotalAge + Cells(i, 3).Value
                End If
            Next i
 
            Cells(11, 8) = CEOcount
            Cells(11, 7) = TotalAge
            '
        End Sub
See the problem?

You can download SmartIndenter free at http://www.oaltd.co.uk/indenter/indentpage.asp, and it works fine in later versions of Excel.
 

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Code:
For i = 2 To 51

 If Cells(i, 5).Value = "CEO" Then
 CEOcount = CEOcount + 1
 TotalAge = TotalAge + Cells(i, 3).Value
 ElseIf Cells(i, 5).Value = "CEO/President" Then
 CEOcount = CEOcount + 1
 TotalAge = TotalAge + Cells(i, 3).Value
 ElseIf Cells(i, 5).Value = "CEO/Chairman" Then
 CEOcount = CEOcount + 1
 TotalAge = TotalAge + Cells(i, 3).Value
 End If

 Next i
You could simply use elseif otherwise.
 

Forum statistics

Threads
1,085,372
Messages
5,383,262
Members
401,820
Latest member
RustEE2020

Some videos you may like

This Week's Hot Topics

Top