An array of Cells?
 

Is there anyway to easily set up an array of cells, wherein each
element of the array would be a cells object? Do I need to set up a
class or is there something in Excel that already does this?

Im using Excel 2002.

-Abe

An array of Cells?
 

And by cells object, I meant range object.
-Abe

An array of Cells?
 

Here is an example that should get you started

Dim aryCells(1 To 5) As Object

Set aryCells(1) = Range("A1:A5")
Set aryCells(2) = Range("H1:H10")
'ETC

MsgBox Application.Sum(aryCells(1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...

And by cells object, I meant range object.
-Abe

An array of Cells?
 

Hi, Abraham

a simple array will do:

Sub RangeArray()
Dim rng(1 To 5) As Range
Dim i As Integer
For i = 1 To 5
Set rng(i) = Range("A1").Offset(i - 1, 0)
Next 'i
For i = 1 To 5
Debug.Print rng(i).Address
Next 'i
End Sub

Regards
Ingolf

An array of Cells?
 

Thanks Bob,

by the way, is there a way I can change the size of that object array?
(i.e. so I create an object called aryCells with indexes 1 thru 5, can
I later modify aryCells to have 15 range objects in it?)
-Abe

Bob Phillips wrote:
Here is an example that should get you started

Dim aryCells(1 To 5) As Object

Set aryCells(1) = Range("A1:A5")
Set aryCells(2) = Range("H1:H10")
'ETC

MsgBox Application.Sum(aryCells(1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...

And by cells object, I meant range object.
-Abe

An array of Cells?
 

Hi Abe,

you can. Just declare the array without dimensioning it in the first
place and do the initial dimensioning in a second step, like

Dim aryCells() As Range
ReDim aryCells(1 To 5)

Later on you then can resize the array using

ReDim Preserve aryCells(1 To 15)

where the keyword 'Preserve' prevents the array's actual content from
being deletet.

Regards
Ingolf

An array of Cells?
 

Sure

Dim aryCells

Redim aryCells(1 To 5)
Set aryCells(1) = Range("A1:A5")
Set aryCells(2) = Range("H1:H10")
'ETC

MsgBox aryCells(1).address

Redim Preserve aryCells(1 To 15)
set aryCells(12) = range("M1:M10")

MsgBox aryCells(12).address

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

wrote in message
oups.com...
Thanks Bob,

by the way, is there a way I can change the size of that object array?
(i.e. so I create an object called aryCells with indexes 1 thru 5, can
I later modify aryCells to have 15 range objects in it?)
-Abe

Bob Phillips wrote:
Here is an example that should get you started

Dim aryCells(1 To 5) As Object

Set aryCells(1) = Range("A1:A5")
Set aryCells(2) = Range("H1:H10")
'ETC

MsgBox Application.Sum(aryCells(1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...

And by cells object, I meant range object.
-Abe