How do you retrieve elements an array set to a range

[ihorwitz]

If I set an array = to a range on a spreadsheet as follows:

Dim vArray()
vArray = Range("h1:h10")

How do I retrieve say h1
vArray(1) does not work?
and then how do I assign another value within vArray for that element?

Tks

 

[Jonmo1]

x = vArray(1, 1)

using a Range as an array, it's a 2D array. You need to specify 2 dimensions (Row, Column)

 

[Damon Ostrander]

Hi jonmo1,

I don't believe you can assign range values to an array in this manner. You can, however do this:

Dim vArray As Range
Set vArray = Range("h1:h10")

after which you can refer to the cells of H1:H10 using vArray(1), vArray(2), etc.

But keep in mind that now the elements of vArray are Range objects, so that vArray(1) is a cell and setting x = vArray(1) puts the value of H1 into x because the Value property of a Range object is the default value. But you could also have x = vArray(1).Interior.ColorIndex to set x to the color index of the cell's fill color. vArray is now essentially a pointer to the range that allows you to refer to the range's cells in array notation.

Damon

 

[Norie]

Damon

Yes you can.

What you've posted creates an reference to the range.

This will create a 2 dimensional array of the values in the range.

vArray = Range("h1:h10")

The obvious advantage of your code is that you have access to all the properties of the cells/range.

The advantage of the array method could be to do with performance of the code.

A lot of folks read range values into arrays, manipulate them in the code and then put them back.

 

[Jonmo1]

It works for me...Try it for yourself
Populate the range H1:H10 with whatever data you want

Dim vArray() 
vArray = Range("h1:h10") 

x = vArray(1,1) 

msgbox x

If I put just numbers 1-10 in H1:H10 respectively...

x = vArray(1,1) - returns 1
x = vArray(2,1) - returns 2
x = vArray(3,1) - returns 3

etc...