Issue with Loop

[jakeman]

Hello everyone - I have a loop statement that's not working correctly and I'm stuck.

What I'm attempting to do is loop through an entire range of values in a single column and where a cell in that range has a value of 1, I return a value from a column adjacent to that column (3 columns away, technically). So far I'm getting some values returned but at the first change of value in the column I'm evaluating, the loop breaks down. Basically, the first two rows in the range are returning fine because they are both 1's, but on the 3rd row the value is a two and my loop doesn't skip over this and find the next value of 1.

Here is my code:

    Set rng = Sheets("Roster").Range("AC6:AC155") 'This column looks at Member Status and returns 1 for Members in good standing and 2 for members not active
    
    x = Application.Dialogs(xlDialogPrinterSetup).Show

    For Each cell In rng
        
        If cell.Value = 1 Then
            Sheets("Letter").Range("B10") = cell.Offset(0, -3)
        End If
        ActiveSheet.PageSetup.Orientation = xlPortrait
        ActiveSheet.PrintOut
    
    Next cell

I'm not sure what syntax is required to tell the loop to move to the next cell in range if value not equal to 1.

 

[MARK858]

Do you not have to move the End If as it currently prints for every cell whatever value is in cell (I can't see a reason why it wouldn't loop though every cell)?

    For Each cell In rng
        
        If cell.Value = 1 Then
            Sheets("Letter").Range("B10") = cell.Offset(0, -3)
        ActiveSheet.PageSetup.Orientation = xlPortrait
        ActiveSheet.PrintOut
        End If
    Next cell

Btw, i wouldn't personally use cell as a variable name.

 

[jakeman]

Yes, that was it. Thanks for your help.

I'm open to any suggestions you have if you think cell as a variable name is no bueno.

 

[MARK858]

You just should try to avoid using variable names that VBA uses in code (even if you just use something like myCell) as it can cause issues.
You will find that some words used in code if you try using them as variable names will actually crash the code (KeyWords).

Anyway happy you got the code working how you want.

https://bettersolutions.com/vba/syntax/keywords.htm