Comparing corresponding cells between multiple named ranges

Assuming you have named ranges "myrange1", "myrange2", and "myrange3" and they are all the same size..

Please Login or Register  to view this content.

Regards, AB

Thanks for the response AB. I have tried incrementing using the code highlighted below. Unless I'm missing something, that code increments the cell down by one row. My problem is my named ranges are non-contiguous cells and the row offset to the next cell varies. That is why I am using the named range. The "for each" loop will increment my named ranges properly, but it only operates on one range at a time (AFAIK). I need to increment the cells from three named ranges within the same "for" loop. Any ideas on how the "next" operator on the "for each" loop works?

Please Login or Register  to view this content.

The r1.cells(i) construct works through a range "across and down". If r1 is, say, A1:C3 and r2 is F4:H6 then

r1.cells(1) = A1 and r2.cells(1) = F4
r1.cells(2) = B1 and r2.cells(2) = G4
r1.cells(3) = C1 and r2.cells(3) = H4
r1.cells(4) = A2 and r2.cells(4) = F5
r1.cells(5) = B2 and r2.cells(5) = G5
r1.cells(6) = C2 and r2.cells(6) = H5
r1.cells(7) = A3 and r2.cells(7) = F6
r1.cells(8) = B3 and r2.cells(8) = G6
r1.cells(9) = C3 and r2.cells(9) = H6

Regards, AB

My problem is that r1 is more like "A1, A9, A15, A22" instead of A1:A22. My other ranges are similarly non-contiguous. Even so the "for each" loop increments the non-contiguous named range properly. I'm looking for the method that the "next" operator in the "for each" loop uses to increment to the next cell.

Ah, OK. I had assumed non-contiguous blocks with the blocks themselves being contiguous.
This modified version taking account of range areas may do the trick for you.

Please Login or Register  to view this content.

Regards, AB

Thanks AB. The areas are what I was missing. Great job!