View Full Version : Solved: Put cell values into array
JimmyTheHand
10-16-2007, 11:57 PM
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
10-17-2007, 12:25 AM
Sub FillArray()
Dim ary
ary = Application.Transpose(Range("C1:C12"))
End Sub
JimmyTheHand
10-17-2007, 12:43 AM
This is great! :)
And can you do it with a non contiguous range? (E.g. Range(C1, C3:C12))
Bob Phillips
10-17-2007, 12:45 AM
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
10-17-2007, 12:56 PM
Thank you, Bob. One can always count on you :thumb
figment
10-17-2007, 02:02 PM
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
10-23-2007, 02:41 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.