My If function won't be taken into account

Question

The macro starts going through every cells from N19 to C14 (left to right). But when it get to C19; problem is it goes to B14 which is out side the table.

I have tried including a few statements like End IF and thought of using the while function but seemed to complexify the problem.

Sub Testtask()
    Dim i As Long, j As Long
    For j = -14 To 3  
        For i = 14 To 19 
            Cells(i, -j).Select
        Next i
    Next j
End Sub

The image shows my working sheet and the table related to in the question:

`For j = 14 to 3 Step -1`. And `Cells(i, j).Select`. Though you normally don't need to `Select`. — BigBen, May 24 '19 at 13:42
[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) (As reference for what BigBen said) — erazorv4, May 24 '19 at 13:46
@BigBen, that sure did do the trick. Thank you so much and to eveyone else that helped out. — Jemmy, May 27 '19 at 06:17

score 2 · Accepted Answer · answered May 24 '19 at 13:55

Write code that reads like you would explain it:

[...] going through every cells from N19 to C14

You want columns to go from N (14) to C (3), and for each column you want to go from 19 to 14. Since both loops are iterating backwards, the part you're missing in your For loop syntax (FWIW they're all keywords, not functions), is the optional Step specifier, which you'll want to specify as -1.

Dim currentColumn As Long
For currentColumn = 14 To 3 Step -1
    Dim currentRow As Long
    For currentRow = 19 To 14 Step -1
        Dim currentCell As Range
        Set currentCell = Sheet1.Cells(currentRow, currentColumn)
        'interact with the currentCell here
    Next
Next

Note that at a glance and without knowing what you're trying to achieve, there isn't really any reason to loop backwards: you could go currentColumn = 3 To 14 and currentRow = 14 To 19, and the exact same cells would be iterated top-to-bottom, left-to-right. Code is easier to follow when things flow naturally: in general its best to avoid looping backwards unless you have to.

Also note the Cells member call is explicitly qualified with a Worksheet object (here Sheet1 - your mileage may vary); if you really mean to work with whatever sheet is currently active, then you can use ActiveSheet as a qualifier and do ActiveSheet.Cells(currentRow, currentColumn).

My If function won't be taken into account

1 Answers1