For Loop not completing

[Yorke]

Hi All,

I am trying to write a code which; when the three columns to the left have values, and there is a new value in Column AM, it shifts the existing values across to the left and places the new value at the end. Once this happens, the code should add 1 to the Cell.Value in Column AN as this serves as a counter of total values seen (as you cant see all of the values when they are shifted/deleted). This should repeat for each cell in column AN. I have something extremely similar earlier in the code which works fine in another location, however this time it doesn't work and I'm not sure why. The code shifts values to the left (that is not the problem), however it does not add 1 to the value in Column AN.

For Each Cell In ThisWorkbook.Worksheets("Status").Range("AN2:AN" & LRS)
If Not IsEmpty(Cell.Offset(0, -4)) And Not IsEmpty(Cell.Offset(0, -3)) And Not IsEmpty(Cell.Offset(0, -2)) And Cell.Offset(0, -2) <> Cell.Offset(0, -1) Then
Cell.Offset(0, -4) = Cell.Offset(0, -3)
Cell.Offset(0, -3) = Cell.Offset(0, -2)
Cell.Offset(0, -2) = Cell.Offset(0, -1)
Cell.Value = Cell.Value + 1
End If
Next Cell

I'm a VBA noob still and I'm sure this is a simple fix! It's just confusing me how it can work earlier but not now!

Thanks in advance for any help!

NB: The similar code which works earlier in the code:
For Each Cell In ThisWorkbook.Worksheets("Status").Range("U2:U" & LRS)
If Not IsEmpty(Cell.Offset(0, -7)) And Not IsEmpty(Cell.Offset(0, -5)) And Not IsEmpty(Cell.Offset(0, -3)) And Cell.Offset(0, -3) <> Cell.Offset(0, -1) Then
Cell.Offset(0, -7) = Cell.Offset(0, -5)
Cell.Offset(0, -6) = Cell.Offset(0, -4)
Cell.Offset(0, -5) = Cell.Offset(0, -3)
Cell.Offset(0, -4) = Cell.Offset(0, -2)
Cell.Offset(0, -3) = Cell.Offset(0, -1)
Cell.Value = Cell.Value + 1
End If
Next Cell

 

[Joe4]

Can you post your ENTIRE code (we cannot see how you are calculating "LRS"), and can you post a small data sample with your expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.