Writing a range to an array...

[Guest]

Hello

How can I use VBA to write a range to an array, change the elements of the
array, and then write back to a worksheet?

I started with this but got an error for the line Data(i)...

Sub ArrayTest()
Dim Data As Variant, i As Integer

Data = Range("A1:A10").Value
For i = LBound(Data) To UBound(Data)
Data(i) = Data(i) + 10
Next i
End Sub

Here I want to take range A1:A10, add 10 to all of the values, and the write
it back to the worksheet.

I want to do this as I think it is faster then maipulating individual cells
in a range, especially when the range is large.

Any help very welcome.

Regards


Alex

 

[Guest]

Alex,
Try this:

Sub ArrayTest()
Dim Data As Variant, i As Integer

Data = Range("A1:A10")
For i = LBound(Data) To UBound(Data)
Data(i, 1) = Data(i, 1) + 10
Next i
Range("A1:A10") = Data
End Sub

HTH

 

[Tom Ogilvy]

Just to add to Toppers excellent suggestion,

Any array formed in this manner will always be a 2D array. Also, it will
always be 1 based regardless of the option base setting.

Dim v as Variant
v = Range(a1:A10).Value is v(1 to 10, 1 to 1)
v = Range(A1:J1).Value is v(1 to 1, 1 to 10)
and of course

v = Range(A1:J10).Value is v(1 to 10, 1 to 10)