If loop not working in VBA

Question

Can anyone help me understand why the code in VBA pasted below is not working?

I am new to VBA, part of the code may look primitive.
The code was supposed to give numbers that are 2 digits and paste it at a location, but it is throwing out at places 3 digit numbers and at random locations.

Dim M As Integer
'M = 24

For i = 7 To 19 And M = 24 Step 1
    If M = 36 Then
        Exit For
    End If

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

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 6) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 7) 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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 8) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 9) 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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 10) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 11) Then
        If ActiveSheet.Cells(8, i) <= ActiveSheet.Cells(23, 11) Then
            ActiveSheet.Cells(M, 8) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(7, i)) * 0.3
        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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 12) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 13) 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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 14) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 15) 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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 16) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 17) 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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 18) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 19) 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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 20) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 21) 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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 22) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 23) 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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 24) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 25) 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
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 26) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 27) 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, 25) - ActiveSheet.Cells(7, i)) / 30 * 100
            ActiveSheet.Cells(M, 17) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 25)) / 30 * 100
        End If
    End If

    If ActiveSheet.Cells(7, i) >= ActiveSheet.Cells(23, 28) And ActiveSheet.Cells(7, i) <= ActiveSheet.Cells(23, 29) 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, 6) = (ActiveSheet.Cells(8, i) - ActiveSheet.Cells(23, 29)) / 30 * 100
        End If
    End If

    M = M + 1
Next i

If `M` is 24 before the loop, then `19 And M = 24` will evaluate to `19`. If `M` is not 24 before the loop, then `19 And M = 24` will evaluate to `0`. `For i = 7 To 0 Step 1` won't do anything because the start value is already greater than the end value. — YowE3K, Sep 20 '17 at 06:50
`ActiveSheet.Cells(x, y).value` prefer this, cell object has a lot of other properties too. — Abhinav Rawat, Sep 20 '17 at 06:50
`If`s are not loop, but branching conditions. `For` is the loop here. — Vincent G, Sep 20 '17 at 06:53
`M = 24` for in the begging and then at the end of very first iteration you have `M = M + 1` executed which makes M = 25, now the condition `For i = 7 To 19 And M = 24 Step 1` will be false because of `AND` so loop will terminate after one iteration. — Abhinav Rawat, Sep 20 '17 at 06:54
@AbhinavRawat The start and end values for `For` loops are only executed once - prior to the loop starting. They are not re-evaluated after each iteration. — YowE3K, Sep 20 '17 at 06:55
@YowE3K Obligatory link: [unless they don't](https://stackoverflow.com/q/42354228/11683) ;) — GSerg, Sep 20 '17 at 07:06
@GSerg It's a good thing that, in this case, the end value is an expression and therefore my statement still holds for this case at least! :D — YowE3K, Sep 20 '17 at 07:19
It is not clear what you are attempting to do. Assuming the value of `M` is 24 prior to the loop (the only way the code will do **anything**) you seem to be updating certain values on row 24 based on info primarily from column G, then updating values on row 25 based on info primarily from column H, then updating values on row 26 based on info primarily from column I, ..., until you finally (when `i` is 18) are updating values on row 35 based on info primarily from column R. Is that the intention? — YowE3K, Sep 20 '17 at 07:35
@Yow3EK you are right, I am trying to do exactly what you mentioned — Karthik P, Sep 20 '17 at 08:14
@YowE3K I changed the code to: Dim M As Integer M = 24 For i = 7 To 19 Step 1 If M = 36 Then Exit For End If — Karthik P, Sep 20 '17 at 08:49
@Abhinav Rawat I changed the initial part to Dim M As Integer M = 24 For i = 7 To 19 Step 1 If M = 36 Then Exit For End If — Karthik P, Sep 20 '17 at 08:52
@KarthikP I also tested it with an example sub in VBA, that is fine, I thought the for loop here works just like other programming languages checking the loop condition every time, so that was my bad. I'll suggest you to use break point and immediate window and use print statements wherever you feel a problem, it will help you in tracking exactly where you have the bug. — Abhinav Rawat, Sep 20 '17 at 09:08
If you change your `For` loop to be `For i = 7 To 18`, you won't need your `If M = 36 Then` test, because `M` is 36 if and only if `i` is 19. — YowE3K, Sep 20 '17 at 19:58
I tried the below pasted loop in an entirely new worksheet, but it is giving the same wrong result. — Karthik P, Sep 24 '17 at 14:41

score 0 · Answer 1 · answered Sep 20 '17 at 06:58

0

Probably pulling the M = 24 before the For will help you getting the desired results:

M = 24
For i = 7 To 19 
....

answered Sep 20 '17 at 06:58

Jochen

1,254
1
7
9

Technically your loop is correct (afte the shown change). If it gives you wrong values in wrong places your If..thens aren't flawless. Try with only the first 2-3 if..thens and check each single value to find where it goes wrong. Without analysing the exact sheet with your data its impossible to help you check your logic. – Jochen Sep 20 '17 at 12:41

If loop not working in VBA

1 Answers1