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
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