For Each Loop, need to skip

Question

I'm having an issue where I get stuck in an infinite loop (infinite through the end of the range, that is) with my For Each Next loop. I know that I can't loop backwards, and of course can't have a standalone "Next":

Dim region As Range
Set region = Range("C4:CC4")

For Each Cell In region
    If Len(Cell) > 0 Then
        Cell.Offset(0, 1) = Cell.Value
        **Need "Next Cell" Here**
    End If
Next Cell

EDIT: Thanks for answers so far, but I think I need to clarify: The issue isn't the infinite loop per se. Right now, it's taking my range and going cell by cell to see if it has something written in the cell. If it does, then it copies it to the one to the right of it and loops. The issue is specifically that if does copy a value, then it needs to skip that cell into which the value was just copied, otherwise it begins the infinite loop. Essentially, I need a way to "Skip" or "Next Cell" in the middle of the If statement.

EDIT 2 Thanks again - Cool Blue's comment's link was the solution!

What Value do you need to place on this "Next Cell"? Check http://stackoverflow.com/a/3875582/194717 http://www.excelfunctions.net/Offset-Function.html — Tony, Nov 13 '14 at 02:00
possible duplicate of [Excel VBA - Exit For loop](http://stackoverflow.com/questions/9414969/excel-vba-exit-for-loop) — Yawar, Nov 13 '14 at 02:20
See http://stackoverflow.com/questions/9414969/excel-vba-exit-for-loop — Yawar, Nov 13 '14 at 02:20
I think maybe [this](http://stackoverflow.com/questions/8680640/vba-how-to-conditionally-skip-a-for-loop-iteration) is more relevant... its hard to tell, your question is not clear. — Cool Blue, Nov 13 '14 at 02:40

score 1 · Answer 1 · edited Nov 13 '14 at 02:25

1

Try this. I hope it helps.

Dim region As Range
Dim firstcell, lastcell As String
firstcell = "C4"
lastcell = "CC4"

Set region = Range(firstcell & ":" & lastcell)

For Each CELL In region
    If Len(CELL) > 0 Then
        CELL.Offset(0, 1) = CELL.Value
        ' **Need "Next Cell" Here**
        If Replace(CELL.Address, "$", "") = lastcell Then Exit For
    End If
Next CELL

edited Nov 13 '14 at 02:25

L42

19,427
11
44
68

answered Nov 13 '14 at 02:21

JeofDC

11
2

score 0 · Accepted Answer · answered Nov 13 '14 at 07:25

I am little late with this answer, but anyway ... it could be very simple and you even do not need any if-statement. The trick is to loop only througt (special) cells which have some (constant) content instead of looping over all the cells. HTH

Dim region As Range
Dim myCell As Range

Set region = Range("C4:CC4")

For Each myCell In region.SpecialCells(xlCellTypeConstants)
    myCell.Offset(0, 1) = myCell.Value
Next myCell

For Each Loop, need to skip

2 Answers2