Styjun

I have an issue I was wondering if anyone could assist me with... I would like to use data type "range" in a similar way to how "collection" works. I would like to use a counter and a loop: "rng(i) = value" where i can be 1, 7, 100 etc. Hence, if I add "A1, A5, C3, D6" to rng, I would like "rng(3)= 3" to set cell C3 equal to 3. Using the "for each x in range" is not an option with regard to how the code are supposed to work. It it possible to make that work?

An alternative solution for me would be if I could add all individual cells in 7 different collections to one variable of data type range.

Any suggestions?

Regards,
Alexander

Unfortunately that would only work if your collection contained a single continuous range.

With a collection of disconnected cells, each cell is its own Area, and trying to directly index the combined Range will give you unexpected results: the index will be applied to the first area, and because it's a single cell area, it will go out of bounds, so e.g. for the range of A1, A5, C3, D6, rng(3) will refer to cell A3 (third cell down relative to A1).

To make indexing work the way you want, you need to mention the Areas property explicitly:

Dim coll As Range

' Set initial contents - has to be at least one cell, can be more
Set coll = some_worksheet.Range("A1,A5,C3") 

' This is how you add to already stored "collection"
Set coll = Application.Union(coll, some_worksheet.Range("D6"))

coll.Areas(3) = 42 ' Sets C3 to 42

I'm not sure exactly what you're asking.

Perhaps this is what you're looking for:

MSDN: Looping Through a Range of
Cells

Another easy way to loop through a range is to use a For Each...Next
loop with the collection of cells specified in the Range property.
Visual Basic automatically sets an object variable for the next cell
each time the loop runs. The following procedure loops through the
range A1:D10, setting to 0 (zero) any number whose absolute value is
less than 0.01.

Sub RoundToZero2() 
 For Each c In Worksheets("Sheet1").Range("A1:D10").Cells 
 If Abs(c.Value) < 0.01 Then c.Value = 0 
 Next 
End Sub

This works with any ranges, contiguous or otherwise

Sub Test()
 Dim rng As Range
 Set rng = Sheet1.Range("A1,B5,E7:E9")

 Dim v As Variant, a As Range, r As Range

 ReDim v(1 To 1)
 For Each a In rng.Areas
 For Each r In a
 v(UBound(v)) = r.Value
 ReDim Preserve v(1 To UBound(v) + 1)
 Next
 Next
 ReDim Preserve v(1 To UBound(v) - 1)

 Debug.Print v(1)
 Debug.Print v(2)
 Debug.Print v(3)

End Sub