Reputation: 33

Next without for error in excel VBA

Pasting below is the part of a code I have written, while running the same, I am getting NEXT without FOR error.

I ran the code without this part to make sure there are no other syntax errors in the for/if loops of code that preceded the one below.

Dim M As Integer
M = 24

For i = 7 To 19 Step 1

    If ActiveSheet.Cells(7, i) >= 0 And ActiveSheet.Cells(8, i) <= 15 Then
        ActiveSheet.Cells(M, 6) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(8, i)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= 345 And ActiveSheet.Cells(8, i) <= 360 Or ActiveSheet.Cells(7, i) >= 345 And ActiveSheet.Cells(8, i) <= 0 Then
        ActiveSheet.Cells(M, 6) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(8, i)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 6) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 7) Then
            ActiveSheet.Cells(M, 6) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 6) = (ActiveSheet.Cells(23, 7) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 7) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 7)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 8) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 9) Then
            ActiveSheet.Cells(M, 7) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 7) = (ActiveSheet.Cells(23, 9) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 8) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 9)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 10) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 11) Then
            ActiveSheet.Cells(M, 8) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 8) = (ActiveSheet.Cells(23, 11) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 9) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 11)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 12) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 13) Then
            ActiveSheet.Cells(M, 9) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 9) = (ActiveSheet.Cells(23, 13) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 10) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 13)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 14) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 15) Then
            ActiveSheet.Cells(M, 10) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 10) = (ActiveSheet.Cells(23, 15) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 11) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 15)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 16) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 17) Then
            ActiveSheet.Cells(M, 11) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 11) = (ActiveSheet.Cells(23, 17) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 12) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 17)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 18) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 19) Then
            ActiveSheet.Cells(M, 12) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 12) = (ActiveSheet.Cells(23, 19) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 13) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 19)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 20) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 21) Then
            ActiveSheet.Cells(M, 13) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 13) = (ActiveSheet.Cells(23, 21) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 14) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 21)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 22) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 23) Then
            ActiveSheet.Cells(M, 14) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 14) = (ActiveSheet.Cells(23, 23) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 15) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 23)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 24) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 25) Then
            ActiveSheet.Cells(M, 15) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 15) = (ActiveSheet.Cells(23, 25) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 16) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 25)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 26) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 27) Then
            ActiveSheet.Cells(M, 16) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 16) = (ActiveSheet.Cells(23, 27) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 17) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 27)) / 30 * 100
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 28) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 29) Then
            ActiveSheet.Cells(M, 17) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) / 30 * 100
        Else
            ActiveSheet.Cells(M, 17) = (ActiveSheet.Cells(23, 29) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 18) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 29)) / 30 * 100
    End If


M = M + 1

Next i

Upvotes: 0

Answers (2)

jsotola

Reputation: 2278

you can shorten your code by using a loop

Dim j As Integer
Dim k As Integer

Dim m As Integer
m = 24

With ActiveSheet
    For i = 7 To 19 Step 1

        If .Cells(7, i) >= 0 And .Cells(8, i) <= 15 Then .Cells(m, 6) = (.Cells(8, i) - .Cells(8, i)) / 30 * 100
        If .Cells(7, i) >= 345 And .Cells(8, i) <= 360 Then .Cells(m, 6) = (.Cells(8, i) - .Cells(8, i)) / 30 * 100

        k = 6                            ' increments by one per loop

        For j = 6 To 28 Step 2

            If .Cells(7, i) >= .Cells(23, j) Then                                      '   6       +2 each loop    j
                If .Cells(8, i) <= .Cells(23, j + 1) Then                              '   7       +2              j+1
                    .Cells(m, k) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100            '   6       +1              k
                Else
                    .Cells(m, k) = (.Cells(23, j + 1) - .Cells(7, i)) / 30 * 100       '   6,7     +1,+2           k  ,j+1
                    .Cells(m, k + 1) = (.Cells(8, i) - .Cells(23, j + 1)) / 30 * 100   '   7,7     +1,+2           k+1,j+1
                End If
            End If
            k = k + 1
        Next j
    m = m + 1
    Next i
End With

Upvotes: 1

paul bica

Reputation: 10705

All inner If's are missing their respective End If's

Here is your code cleaned up a bit, and fixed:

Option Explicit

Public Sub TestSyntax()
    Dim m As Long, i As Long

    m = 24

    With ActiveSheet

        For i = 7 To 19 Step 1

            If .Cells(7, i) >= 0 And .Cells(8, i) <= 15 Then
                .Cells(m, 6) = (.Cells(8, i) - .Cells(8, i)) / 30 * 100
            End If

            If .Cells(7, i) >= 345 And .Cells(8, i) <= 360 Or .Cells(7, i) >= 345 And .Cells(8, i) <= 0 Then
                .Cells(m, 6) = (.Cells(8, i) - .Cells(8, i)) / 30 * 100
            End If

            If .Cells(7, i) >= .Cells(23, 6) Then
                If .Cells(8, i) <= .Cells(23, 7) Then
                    .Cells(m, 6) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 6) = (.Cells(23, 7) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 7) = (.Cells(8, i) - .Cells(23, 7)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 8) Then
                If .Cells(8, i) <= .Cells(23, 9) Then
                    .Cells(m, 7) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 7) = (.Cells(23, 9) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 8) = (.Cells(8, i) - .Cells(23, 9)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 10) Then
                If .Cells(8, i) <= .Cells(23, 11) Then
                    .Cells(m, 8) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 8) = (.Cells(23, 11) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 9) = (.Cells(8, i) - .Cells(23, 11)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 12) Then
                If .Cells(8, i) <= .Cells(23, 13) Then
                    .Cells(m, 9) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 9) = (.Cells(23, 13) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 10) = (.Cells(8, i) - .Cells(23, 13)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 14) Then
                If .Cells(8, i) <= .Cells(23, 15) Then
                    .Cells(m, 10) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 10) = (.Cells(23, 15) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 11) = (.Cells(8, i) - .Cells(23, 15)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 16) Then
                If .Cells(8, i) <= .Cells(23, 17) Then
                    .Cells(m, 11) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 11) = (.Cells(23, 17) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 12) = (.Cells(8, i) - .Cells(23, 17)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 18) Then
                If .Cells(8, i) <= .Cells(23, 19) Then
                    .Cells(m, 12) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 12) = (.Cells(23, 19) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 13) = (.Cells(8, i) - .Cells(23, 19)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 20) Then
                If .Cells(8, i) <= .Cells(23, 21) Then
                    .Cells(m, 13) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 13) = (.Cells(23, 21) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 14) = (.Cells(8, i) - .Cells(23, 21)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 22) Then
                If .Cells(8, i) <= .Cells(23, 23) Then
                    .Cells(m, 14) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 14) = (.Cells(23, 23) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 15) = (.Cells(8, i) - .Cells(23, 23)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 24) Then
                If .Cells(8, i) <= .Cells(23, 25) Then
                    .Cells(m, 15) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 15) = (.Cells(23, 25) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 16) = (.Cells(8, i) - .Cells(23, 25)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 26) Then
                If .Cells(8, i) <= .Cells(23, 27) Then
                    .Cells(m, 16) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 16) = (.Cells(23, 27) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 17) = (.Cells(8, i) - .Cells(23, 27)) / 30 * 100
                End If
            End If

            If .Cells(7, i) >= .Cells(23, 28) Then
                If .Cells(8, i) <= .Cells(23, 29) Then
                    .Cells(m, 17) = (.Cells(8, i) - .Cells(7, i)) / 30 * 100
                Else
                    .Cells(m, 17) = (.Cells(23, 29) - .Cells(7, i)) / 30 * 100
                    .Cells(m, 18) = (.Cells(8, i) - .Cells(23, 29)) / 30 * 100
                End If
            End If

            m = m + 1
        Next

    End With

End Sub

Upvotes: 1

Next without for error in excel VBA

Answers (2)

Related Questions