Solved: Put cell values into array

[JimmyTheHand]

There is a range and there is an array variable. I want to put the cell values into the array. E.g.

Sub FillArray()
Dim Ar(1, 12) As String, i As Long
For i = 1 To 12
Ar(i) = ActiveSheet.Range("C" & i)
Next
End Sub

Can you do the same without a loop?

Jimmy

[Bob Phillips]

Sub FillArray()
Dim ary
ary = Application.Transpose(Range("C1:C12"))
End Sub

[JimmyTheHand]

This is great! :)
And can you do it with a non contiguous range? (E.g. Range(C1, C3:C12))

[Bob Phillips]

Don't think so, if you use



ary = Application.Transpose(Range("C1:C12,C20"))


it errors, if you use



ary = Application.Transpose(Range("C1:C12", "C20"))


it picks all intermeditae cells as well.

[JimmyTheHand]

Thank you, Bob. One can always count on you :thumb

[figment]

sorry to post on a solved thread. but is there a way to copy more then just the cell value into the array, without looping all the cells? i have a function were i need the border infor for the cells, and if i can just make an aray of all of them like you are doing here with the values then it will speed my function up by a lot.

[crazyfray]

sorry to post on a solved thread. but is there a way to copy more then just the cell value into the array, without looping all the cells? i have a function were i need the border infor for the cells, and if i can just make an aray of all of them like you are doing here with the values then it will speed my function up by a lot.

I guess that'd just be a 2 dimension array with the border value (X.borders) as the second dimension. Since I tend to mess up with multidimension arrays, I would have 2 arrays, and just check and write each at the same time every time, always writing even if blank, so they can't possibly (he says!) be in the wrong place.

Pseudo-code alert!

aArray1(x) = cell.Value
aArray2(x) = cell.borders