VBA: Reason for Range Variable not working

[AD_Taylor]

Hi,

So I've declared and set a Range variable, lets call it checkRng for now.
I've also set up another Range variable lets say currCell.

So:

Dim checkRng As Range
Dim currCell As Range

Set checkRng = Range("A1:A245")

Now when I try and start a loop so that I am checking every cell in checkRng individually, I always thought I could do this:

For each currCell In checkRng

'Do some stuff here with currCell.Value etc

Next currCell

However, sometimes this works and sometimes it does not.
I have 2 macros (can post the code if needed) where this code works in one and does not work in the other.

In the one that it doesn't work in if I type ?currCell.Address in the Immediate window while debugging I can see that currCell has strangely taken the entire range from checkRng, so in this case currCell = A1:A245.

I've had to resort to counting rows and columns and looping based on these numbers using Cells(rowNum, colNum).Value.

Any ideas as to why this happens?

Thanks,
Adam

 

[RoryA]

It is safer to specify what you are looping through:

For each currCell In checkRng.Cells

'Do some stuff here with currCell.Value etc

Next currCell

 

[AD_Taylor]

Perfect!

I'll do this from now on - hopefully I remember!

Thanks again,
Adam