CODEWITHSUNDEEP
excelvbafor-loop
user2829591
user2829591

Reputation: 71

Next Without For Error VBA

I have the following code and VBA is giving me a "Next Without For" Error when I definitely have both. I know that VBA can list errors that are not exactly the same as what it says they are, but I can't find any other closed loops. If someone could check this out, that would be awesome! Thanks:

Option Explicit
Sub HW09()

    Dim ng As Integer
    Dim v As String
    Dim lg As String
    Dim ca As Integer
    Dim sd As Integer
    Dim c As Integer
    Dim r As Integer

    c = 2

    Do
        ng = InputBox("Please enter the student's numerical grade.")
        If ng < 0 Then
            ng = 0
        If ng > 100 Then
            ng = 100
        End If

        Cells(c, 2).Value (ng)
        c = c + 1

        v = InputBox("Would you like to enter another grade? Type 'Y' for yes and 'N' for no.")
        If v = "N" Then Exit Do
        End If

    Loop

    Cells(1, 2).Value ("Numerical Grade")
    Cells(1, 1).Value ("Letter Grade")

    For r = 1 To c
        If Cells(r, 2) >= 90 Then
            lg = "A"
            Cells(r, 1).Value (lg)
        If Cells(r, 2) >= 80 Then
            lg = "B"
            Cells(c, 1).Value (lg)
        If Cells(r, 2) >= 70 Then
            lg = "C"
            Cells(c, 1).Value (lg)
        If Cells(r, 2) >= 60 Then
            lg = "D"
            Cells(c, 1).Value (lg)
        Else
            lg = "F"
            Cells(c, 1).Value (lg)
        End If

        r = r + 1

    Next r

    c = c - 1

    ca = Application.WorksheetFunction.Average("(1,2):(1,c)")
    If ca >= 90 Then
        lg = "A"
    If ca >= 80 Then
        lg = "B"
    If ca >= 70 Then
        lg = "C"
    If ca >= 60 Then
        lg = "D"
    Else
        lg = "F"
    End If

    MsgBox ("The average letter grade for these " & (c) & " students is " & (lg) & ".")
    sd = c * (Application.WorksheetFunction.Sum("(1, 2)(1, c) ^ 2)")-Application.WorksheetFunction.Sum("(1, 2)(1, c)") ^ 2 / (c * (c - 1)))
    MsgBox ("The standard deviation for these grades is" & (sd) & ".")

End Sub

Upvotes: 7

Views: 80842

Answers (4)

Greg J
Greg J

Reputation: 379

Every IF statement needs to be terminated with an ENDIF.
Within the FOR/NEXT loop you have 4 IFs, one ELSE and one ENDIF this needs to be changed to:

IF Condition1 THEN
  'code
 ELSEIF Condition2 THEN
  'code
 ELSEIF Condition3 THEN
  'code
 ELSEIF Condition4 THEN
  'code
 ELSE 'All other possibilities
  'code
ENDIF

Upvotes: 8

gadolf
gadolf

Reputation: 1065

This error occurs when the condition is not closed. You must don't forger close if conditions.

for example:

Public Sub start_r()

    LastRow = SPT_DB.Range("D" & Rows.count).End(xlUp).Row

Dim i As Long
For i = 3 To 132

    State = Cells(1, i)

    Dim j As Long
    For j = 2 To LastRow

        m = SPT_DB.Cells(j, 4).Value
        z = SPT_DB.Cells(j, 5).Value
        n1 = SPT_DB.Cells(j, 6).Value
        fc = SPT_DB.Cells(j, 7).Value
        am = SPT_DB.Cells(j, 8).Value
        sp = SPT_DB.Cells(j, 10).Value
        sr = SPT_DB.Cells(j, 11).Value
        liq = SPT_DB.Cells(j, 13).Value

        num1 = Val(Left(State, 1))
        num2 = Val(Mid(State, 3, 1))
        num3 = Val(Mid(State, 5, 1))
        num4 = Val(Mid(State, 7, 1))
        num5 = Val(Mid(State, 9, 1))

        Dim spt_class As spt_class
        Set spt_class = New spt_class

        Select Case num1
            Case Is = 1: Call spt_class.rd_r1
            Case Is = 2: Call spt_class.rd_r2
            Case Is = 3: Call spt_class.rd_r3
            Case Is = 4: Call spt_class.rd_r4
        End Select

        Select Case num2
            Case Is = 1: Call spt_class.msf_r1
            Case Is = 2: Call spt_class.msf_r2
            Case Is = 3: Call spt_class.msf_r3
            Case Is = 4: Call spt_class.msf_r4
            Case Is = 5: Call spt_class.msf_r5
            Case Is = 6: Call spt_class.msf_r6
        End Select

        Select Case num3
            Case Is = 0:
            Case Is = 1: Call spt_class.n1_cs_r1
            Case Is = 2: Call spt_class.n1_cs_r2
            Case Is = 3: Call spt_class.n1_cs_r3
        End Select

        Select Case num4
            Case Is = 0:
            Case Is = 1: Call spt_class.dr_r1
            Case Is = 2: Call spt_class.dr_r2
            Case Is = 3: Call spt_class.dr_r3
            Case Is = 4: Call spt_class.dr_r4
        End Select

        Select Case num5
            Case Is = 1: Call spt_class.crr_r1
            Case Is = 2: Call spt_class.crr_r2
            Case Is = 3: Call spt_class.crr_r3
            Case Is = 4: Call spt_class.crr_r4
            Case Is = 5: Call spt_class.crr_r5
            Case Is = 6: Call spt_class.crr_r6
            Case Is = 7: Call spt_class.crr_r7
            Case Is = 8: Call spt_class.crr_r8
            Case Is = 9: Call spt_class.crr_r9
        End Select

        Call spt_class.lvr_r

    Next j


        If cnt_f_1_all = 0 Then
            Cells(4, i) = 0
        Else
            Cells(4, i) = cnt_f_1_liq * 100 / cnt_f_1_all
            Cells(4, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_2_all = 0 Then
            Cells(5, i) = 0
        Else
            Cells(5, i) = cnt_f_2_liq * 100 / cnt_f_2_all
            Cells(5, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_3_all = 0 Then
            Cells(6, i) = 0
        Else
            Cells(6, i) = cnt_f_3_liq * 100 / cnt_f_3_all
            Cells(6, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_4_all = 0 Then
            Cells(7, i) = 0
        Else
            Cells(7, i) = cnt_f_4_liq * 100 / cnt_f_4_all
            Cells(7, i).NumberFormat = "#,##0.00"
        End If


        If cnt_f_n0_1_all = 0 Then
            Cells(14, i) = 0
        Else
            Cells(14, i) = cnt_f_n0_1_liq * 100 / cnt_f_n0_1_all
            Cells(14, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n0_2_all = 0 Then
            Cells(15, i) = 0
        Else
            Cells(15, i) = cnt_f_n0_2_liq * 100 / cnt_f_n0_2_all
            Cells(15, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n0_3_all = 0 Then
            Cells(16, i) = 0
        Else
            Cells(16, i) = cnt_f_n0_3_liq * 100 / cnt_f_n0_3_all
            Cells(16, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n0_4_all = 0 Then
            Cells(17, i) = 0
        Else
            Cells(17, i) = cnt_f_n0_4_liq * 100 / cnt_f_n0_4_all
            Cells(17, i).NumberFormat = "#,##0.00"
        End If


        If cnt_f_n1_1_all = 0 Then
            Cells(24, i) = 0
        Else
            Cells(24, i) = cnt_f_n1_1_liq * 100 / cnt_f_n1_1_all
            Cells(24, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n1_2_all = 0 Then
            Cells(25, i) = 0
        Else
            Cells(25, i) = cnt_f_n1_2_liq * 100 / cnt_f_n1_2_all
            Cells(25, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n1_3_all = 0 Then
            Cells(26, i) = 0
        Else
            Cells(26, i) = cnt_f_n1_3_liq * 100 / cnt_f_n1_3_all
            Cells(26, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n1_4_all = 0 Then
            Cells(27, i) = 0
        Else
            Cells(27, i) = cnt_f_n1_4_liq * 100 / cnt_f_n1_4_all
            Cells(27, i).NumberFormat = "#,##0.00"
        End If


        If cnt_f_n2_1_all = 0 Then
            Cells(34, i) = 0
        Else
            Cells(34, i) = cnt_f_n2_1_liq * 100 / cnt_f_n2_1_all
            Cells(34, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n2_2_all = 0 Then
            Cells(35, i) = 0
        Else
            Cells(35, i) = cnt_f_n2_2_liq * 100 / cnt_f_n2_2_all
            Cells(35, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n2_3_all = 0 Then
            Cells(36, i) = 0
        Else
            Cells(36, i) = cnt_f_n2_3_liq * 100 / cnt_f_n2_3_all
            Cells(36, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n2_4_all = 0 Then
            Cells(37, i) = 0
        Else
            Cells(37, i) = cnt_f_n2_4_liq * 100 / cnt_f_n2_4_all
            Cells(37, i).NumberFormat = "#,##0.00"
        End If


        If cnt_f_n3_1_all = 0 Then
            Cells(44, i) = 0
        Else
            Cells(44, i) = cnt_f_n3_1_liq * 100 / cnt_f_n3_1_all
            Cells(44, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n3_2_all = 0 Then
            Cells(45, i) = 0
        Else
            Cells(45, i) = cnt_f_n3_2_liq * 100 / cnt_f_n3_2_all
            Cells(45, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n3_3_all = 0 Then
            Cells(46, i) = 0
        Else
            Cells(46, i) = cnt_f_n3_3_liq * 100 / cnt_f_n3_3_all
            Cells(46, i).NumberFormat = "#,##0.00"
        End If
        If cnt_f_n3_4_all = 0 Then
            Cells(47, i) = 0
        Else
            Cells(47, i) = cnt_f_n3_4_liq * 100 / cnt_f_n3_4_all
            Cells(47, i).NumberFormat = "#,##0.00"
        End If

Next i

        cnt_f_1_liq = 0
        cnt_f_2_liq = 0
        cnt_f_3_liq = 0
        cnt_f_4_liq = 0
        cnt_f_1_all = 0
        cnt_f_2_all = 0
        cnt_f_3_all = 0
        cnt_f_4_all = 0

        cnt_f_n0_1_liq = 0
        cnt_f_n0_2_liq = 0
        cnt_f_n0_3_liq = 0
        cnt_f_n0_4_liq = 0
        cnt_f_n0_1_all = 0
        cnt_f_n0_2_all = 0
        cnt_f_n0_3_all = 0
        cnt_f_n0_4_all = 0

        cnt_f_n1_1_liq = 0
        cnt_f_n1_2_liq = 0
        cnt_f_n1_3_liq = 0
        cnt_f_n1_4_liq = 0
        cnt_f_n1_1_all = 0
        cnt_f_n1_2_all = 0
        cnt_f_n1_3_all = 0
        cnt_f_n1_4_all = 0

        cnt_f_n2_1_liq = 0
        cnt_f_n2_2_liq = 0
        cnt_f_n2_3_liq = 0
        cnt_f_n2_4_liq = 0
        cnt_f_n2_1_all = 0
        cnt_f_n2_2_all = 0
        cnt_f_n2_3_all = 0
        cnt_f_n2_4_all = 0

        cnt_f_n3_1_liq = 0
        cnt_f_n3_2_liq = 0
        cnt_f_n3_3_liq = 0
        cnt_f_n3_4_liq = 0
        cnt_f_n3_1_all = 0
        cnt_f_n3_2_all = 0
        cnt_f_n3_3_all = 0
        cnt_f_n3_4_all = 0

End Sub

Upvotes: 2

David Zemens
David Zemens

Reputation: 53623

I think the nested If statements inside For r = 1 to c... don't close properly? Generally, each If also requires an End If, and you only have one End If statement. This is causing the compiler to reach the Next r statement while it's still "inside" an If block, thus the error raises, and makes sense.

You may look in to using a Select Case switch instead of nesting several If/Then statements. In my experience, they're more easy to interpret when you're debugging. Something like:

For r = 1 To c
    Select Case Cells(r,2)
        Case >= 90
           lg = "A"

        Case >= 80
           lg = "B"

        Case >= 70
           lg = "C"

        Case >= 60
           lg = "D"
        Case Else
           lg = "F"
     End Select
     Cells(r,1).Value = lg


r = r + 1  '## You may want to omit this line, see my comments.


Next r

Note: You may want to omit the r = r+1 unless you're intending to skip every other record, the Next statement automatically increments r by a value of 1 unless otherwise specified.

If you do intend to skip every other record, you should do For r = 1 to c Step 2 and likewise omit the r = r+1 .

Upvotes: 5

Drew Chapin
Drew Chapin

Reputation: 7989

Your problem is you are doing If... Then... If... Then... instead of If... Then... ElseIf... Then... 

If Cells(r, 2) >= 90 Then
    lg = "A"
    Cells(r, 1).Value (lg)
ElseIf Cells(r, 2) >= 80 Then
    lg = "B"
    Cells(c, 1).Value (lg)
ElseIf Cells(r, 2) >= 70 Then
    lg = "C"
    Cells(c, 1).Value (lg)
ElseIf Cells(r, 2) >= 60 Then
    lg = "D"
    Cells(c, 1).Value (lg)
Else
    lg = "F"
    Cells(c, 1).Value (lg)
End If

Upvotes: 13

Related Questions