Curious "For Each Cell in Range" issue

[Fishboy]

Hi all,

I have been trying to get my around around a strange issue I have been having with some of my macros. I was very pleased with myself recently when I got to grips with "For Each Cell in Range" VBA however I have started noticing that sometimes these don't work as expected.

For example, the following code works as intended:

Sub TEST1()
Dim Cell As Range, cRange As Range
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set cRange = Range("A2:A" & LastRow)
    For Each Cell In cRange
        If Cell.Value <= 20 And Cell.Value >= 16 Then
            Cell.Offset(0, 1).Value = "AAA"
        End If
    Next Cell
End Sub

It looks through column A and for each cell in cRange that is between 16 and 20 it applies a value to the adjacent cell. This works all the way down the column and is truly checking "Each Cell" in the range.

Now this next code however:

Sub TEST2()
Dim Cell As Range, cRange As Range
    LastRowI = Sheets("Sheet2").Cells(Rows.Count, "I").End(xlUp).Row + 1
    LastRowA = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set cRange = Sheets("Sheet1").Range("A2:A" & LastRowA)
    For Each Cell In cRange
        If Cell.Value = "Update" Then
            Range(Cell, Cell.Offset(0, 1)).Copy _
                Destination:=Sheets("Sheet2").Range("I" & LastRowI)
        End If
    Next Cell
End Sub

Again, it is supposed to be looking through Sheet1 column A and for each cell in cRange that has a value of "Update" it copies the cell and adjacent cell to the next blank row of column I on Sheet2. This only seems to grab one row when in my test data it should have found several.

How come the first code truly checks "each cell" and moves to the next when the second code seems to fall over after finding a hit?

The above examples have been cropping up more and more lately and i cannot figure out the massive difference between the two.

 

[RoryA]

You never change lastrowI so you just overwrite the same row with each copy.

 

[Fishboy]

You never change lastrowI so you just overwrite the same row with each copy.

Hi Rory, thanks for the feedback.

Doesn't LastRowI = Sheets("Sheet2").Cells(Rows.Count, "I").End(xlUp).Row + 1 mean whatever is currently the last row, plus 1?

If not, where should I be incrementing this by 1 each time?

 

[RoryA]

It does but you don't change that each time you paste, so you just paste over it again. You need:

Sub TEST2()
Dim Cell As Range, cRange As Range
    LastRowI = Sheets("Sheet2").Cells(Rows.Count, "I").End(xlUp).Row + 1
    LastRowA = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set cRange = Sheets("Sheet1").Range("A2:A" & LastRowA)
    For Each Cell In cRange
        If Cell.Value = "Update" Then
            Range(Cell, Cell.Offset(0, 1)).Copy _
                Destination:=Sheets("Sheet2").Range("I" & LastRowI)
            LastRowI = LastRowI + 1
        End If
    Next Cell
End Sub

 

[Fishboy]

It does but you don't change that each time you paste, so you just paste over it again. You need:

Sub TEST2()
Dim Cell As Range, cRange As Range
    LastRowI = Sheets("Sheet2").Cells(Rows.Count, "I").End(xlUp).Row + 1
    LastRowA = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set cRange = Sheets("Sheet1").Range("A2:A" & LastRowA)
    For Each Cell In cRange
        If Cell.Value = "Update" Then
            Range(Cell, Cell.Offset(0, 1)).Copy _
                Destination:=Sheets("Sheet2").Range("I" & LastRowI)
            LastRowI = LastRowI + 1
        End If
    Next Cell
End Sub

Thanks mate, that makes a lot of sense and explains why a lot of my code has been misbehaving!

 

[RoryA]

Glad to help.

Code has an unfortunate habit of doing what we tell it to rather than necessarily what we want it to.

 

[Fishboy]

Glad to help.

Code has an unfortunate habit of doing what we tell it to rather than necessarily what we want it to.

The cheeky swine!