0

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?

Doublus
  • 17
  • 3
  • 1
    You seem to be mixing up an if statement into your loop's first line. Step one would be learning how to construct an `If` statement. Have you looked into that yet? https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-ifthenelse-statements – braX Mar 28 '23 at 11:30
  • 1
    And here is how to make a loop - https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/fornext-statement – braX Mar 28 '23 at 11:35
  • Your code does not make sense and can't be reproduced. – Foxfire And Burns And Burns Mar 28 '23 at 11:41

1 Answers1

0

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.

FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Thank you Thomas it worked and I used the your tip and specified the worksheet as well. And double thanks for the detailed step by step process! I learned a lot :) edit: I sadly cannot upvote your comment, but nevertheless here a virtual vote :D – Doublus Mar 28 '23 at 11:55