Run code on first 20 rows if column is indicated as X

I’ve written my code to run on the second row and now I want to let it run on the first 20 rows if I indicate column 23 with an "x".

I can’t seem to make it work somehow:

Dim i as Long
Dim j as long
Dim Seat as String
i = 2

For i = 2 To 20 And Cells(i, 23).Value = "x"
j = i
Seat = Cells(j, 5) 'an example of a string that uses i as basis

'... my code

Next i

End Sub

When I run the macro, it runs the code on each row even tough it isn’t marked with an "x"

Can someone tell me the proper way to loop the code for each row if it’s marked in the column to run?

>Solution :

The for-command doesn’t allow any extra condition, it runs a loop for a specific number of iterations.

Put the condition in an extra If-statement:

For i = 2 To 20 
    If Cells(i, 23).Value = "x" Then
        ...
    End If
Next i

Hint: Tell VBA always on which worksheet you want to work. With your code, you will work on the active sheet and that is not always the sheet you want. For more details, take the time and read How to avoid using Select in Excel VBA

For those who are curious what will happen when writing

i = 2
For i = 2 To 20 And Cells(i, 23).Value = "x"
   (...)
Next

VBA will evaluate the term 20 And Cells(i, 23).Value = "x" and convert it into a number. It will first check the value of Cells(2, 23): If this is "x", the term Cells(i, 23).Value = "x" will be True, which is represented as -1, which is a bit pattern where all bits are set to 1. Now VBA will evaluate 20 And -1 (using arithmetic And) and this results in 20.

This term is evaluated only once, before the loop starts, and therefore the loop will run from 2 to 20.

If, however, the value in Cells(i, 23) is something different, the term Cells(i, 23).Value = "x" will be False, which is represented as 0 (all bits are 0). 20 And 0 will be 0 and therefore the loop will not run at all.

Leave a Reply