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 stackoverflow.com/a/3875582/194717 excelfunctions.net/Offset-Function.html — Tony
– Tony, Commented Nov 13, 2014 at 2:00
See stackoverflow.com/questions/9414969/excel-vba-exit-for-loop — Yawar
– Yawar, Commented Nov 13, 2014 at 2:20
I think maybe this is more relevant... its hard to tell, your question is not clear. — Cool Blue
– Cool Blue, Commented Nov 13, 2014 at 2:40

L42 · Accepted Answer · 2014-11-13 02:25:40Z

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, 2014 at 2:25

L42

19.8k11 gold badges47 silver badges70 bronze badges

answered Nov 13, 2014 at 2:21

JeofDC

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gembird · Accepted Answer · 2014-11-13 07:25:28Z

0

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

answered Nov 13, 2014 at 7:25

gembird

14.1k5 gold badges39 silver badges54 bronze badges

Collectives™ on Stack Overflow

For Each Loop, need to skip

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related