[RESOLVED] Indexing a Range Yields Cell Outside the Range

[caffeine]

There must be something fundamental I don't understand about indexing ranges because I'm a little stumped.

I have a range, c_rng, and when I iterate using something like this:

for i = 1 to c_rng.count
     c_rng(i).select
next i

cells outside c_rng get selected! Here's the actual code:

Public Sub initShape(point As Byte, shape As ShapeEnum)

    With Worksheets("board")

        ElseIf shape = SHAPE_AREA Then
            Dim rng1 As Range, rng2 As Range, rng3 As Range
            Set rng1 = .Range("row" & areaRow(point))
            Set rng2 = .Range("col" & areaCol(point))
            Set rng3 = .Range("grid" & sectionByRC(areaRow(point), areaCol(point)))
            Set c_rng = ProperUnion(rng1, rng2, rng3)
            c_shape = SHAPE_AREA
        End If
    End With

    ' Create an array of CCells, each CCell is the abstraction of an Excel cell
    ' (with extra properties).  Order will be the same order as rng(i) as i varies
    ' from 1 to the last Cell in the range.
    Dim i As Byte
    ReDim c_arr(1 To c_rng.count)
    For i = 1 To UBound(c_arr)
        Set c_arr(i) = New CCell
        Call c_arr(i).setCell_Range(c_rng(i))
    Next i
End Sub

The function ProperUnion() is Chip Pearson's code which performs range unions without the "double counting" you get when you try to union overlapping ranges (it removes the intersection).

I confirmed that:

• rng1 is correct (via rng1.select in the Immediate window)
• rng2 is correct (via rng2.select in the Immediate window)
• rng3 is correct (via rng3.select in the Immediate window)
• c_rng is correct (via c_rng.select in the Immediate window)
• c_rng.count is correct

In this image, the yellow cells are the ones that get selected when I use c_rng.select. The numbers show what gets selected when I use c_rng(i).select. Starting with 13, they fall outside of c_rng.



I thought we can index ranges relative to the range's "start". Am I not understanding range indexing?

[westconn1]

try using

for each cel in c_rng

though some cells may get selected more than once

[caffeine]

OK, with a little experimentation I determined that indexing a non-contiguous range of cells simply doesn't work. I never knew that.

So yeah, a for each construct with a counter variable is the proxy for range indexing for non-contiguous ranges.