Writing a range to an array...

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

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


"Alex" wrote:

> 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

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)

--
Regards,
Tom Ogilvy



"Toppers" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Alex" wrote:
>
> > 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