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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top