0

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
Improve this question
4
  • Probably a duplicate: stackoverflow.com/q/29021377/1531971 (But you should indicate that you did some research on this very common problem.) Commented Sep 16, 2017 at 14:08
  • why are you using ActiveSheet.Cells(8, i) - ActiveSheet.Cells(8, i) ? just put in 0 Commented Sep 17, 2017 at 3:53
  • what happens if someone changes to another worksheet while the code is executing? Commented Sep 17, 2017 at 5:29
  • @paul bica thanks for the help Commented Sep 20, 2017 at 6:39

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

just leave out ActiveSheet and the dot references altogether, the code will be the same, and just as buggy
@jsotola :) (just trying to show how to be explicit everywhere, including when working with the ActiveSheet)
1

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
Improve this answer

1 Comment

I had to edit some parts to take care few other calculations, pasting the code below, it is not working as intended:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.