Microsoft Excel - Assign values from two columns into array

I'm trying to populate an array with two dimensions "Staff Number" and "Hours Worked"

I know that I can assign an array like this ary = range("A1:C10").cells, but I'm trying to assign the values from two columns like this
arySIP() = Range("D21:D294,O21:O294").Cells.

When doing it this way, I only get the details for data in column D (Staff Number).

Is there anyway I can get the two columns of data into two seperate array elements without having to write further code to cycle through each cell?

My VB is pretty rusty but don't you have to size the array first if you are going to use an array of arrays.

Dim arySIP() as Array() '  This is still an empty array at this point.

Redim Preservice arySIP(1)

arySIP(1) = your range code.

Something like:

Dim arr as Variant
arr = Range("B6:H14").Value

Thanks for the help guys,

But that wasn't quite what I was after. I want to initialise the array in one go to include data from two seperate columns.

This piece of code does the trick
arySIP() = Array(Range("D21:D294").Formula, Range("O21:O294").Formula)

If I used .values or .cells instead of .formula, the array contains all the other information regarding cell formats etc

Thanks again

Pete

Hi Pete,
I am a bit mystified.
Range.Formula would return the formula contained in a cell as a string or the value if the cell contains a constant or a null if the cell is empty.
Range.Value would return the value that shows in a cell either as a constant or as the result of a formula.  This is what I  inperpreted what you meant by the data in the cells
Range.Cells requires an index to specify a particular cell and as faar as I know does not of itself return anything.
Glad that you got the result you wanted by whatever means.
Regards
Harry

Hi Harry,

I agree with you. I think there is are some issues with this project and our general IT.

I normally use .values to return the raw data, but for some reason it's returning all the cell information e.g. formats, links etc... I can use .cells.value, but shouldn't need to.

As the cells I'm using don't contain any formula's, .formula works for me in this instance.

To be honest, we're undergoing several major IT infrastructure upgrades and things aren't working as they should in general, so I'm putting this glitch down to unforseen gremlins caused by the IT boffins. I'll see what happens when they roll out Office 2010 and raise incidents once rolled out.

Cheers