I thought I'd done this in the past, but I can't remember if or how. I
want be able to assign an array of values to a range in 1 go, such as

1 2 3
4 5 6
7 8 9

to A1:C3

I'd thought I'd done something like Range("A1:C3") =
(1,2,3;4,5,6;7,8,9)
using ; as a delimiter between rows in the array, but that clearly
isn't working.

I'm quite familiar with the looping/iterator techniques available to
assign a variable array to a range. I was just hoping there was a way
to do this in one line of code.

You can use array constants in Excel formulae, but I don't think VBA has
multidimensional array constants. You should be able to assign your data to
a 2 dimensional array variable and set your range equal to that

Range("A1:C3").Value = arrData

where arrData is a 3x3 array.


"dodgo" wrote:

> I thought I'd done this in the past, but I can't remember if or how. I
> want be able to assign an array of values to a range in 1 go, such as
>
> 1 2 3
> 4 5 6
> 7 8 9
>
> to A1:C3
>
> I'd thought I'd done something like Range("A1:C3") =
> (1,2,3;4,5,6;7,8,9)
> using ; as a delimiter between rows in the array, but that clearly
> isn't working.
>
> I'm quite familiar with the looping/iterator techniques available to
> assign a variable array to a range. I was just hoping there was a way
> to do this in one line of code.
>
>

Sub AABB()
Range("A1:C3").Value = _
Evaluate("{1,2,3;4,5,6;7,8,9}")

End Sub

works for small amounts of data.

--
Regards,
Tom Ogilvy


"dodgo" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I thought I'd done this in the past, but I can't remember if or how. I
> want be able to assign an array of values to a range in 1 go, such as
>
> 1 2 3
> 4 5 6
> 7 8 9
>
> to A1:C3
>
> I'd thought I'd done something like Range("A1:C3") =
> (1,2,3;4,5,6;7,8,9)
> using ; as a delimiter between rows in the array, but that clearly
> isn't working.
>
> I'm quite familiar with the looping/iterator techniques available to
> assign a variable array to a range. I was just hoping there was a way
> to do this in one line of code.
>

I always overlook the the opportunities to use Evaluate. Thanks Tom.

"Tom Ogilvy" wrote:

> Sub AABB()
> Range("A1:C3").Value = _
> Evaluate("{1,2,3;4,5,6;7,8,9}")
>
> End Sub
>
> works for small amounts of data.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "dodgo" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I thought I'd done this in the past, but I can't remember if or how. I
> > want be able to assign an array of values to a range in 1 go, such as
> >
> > 1 2 3
> > 4 5 6
> > 7 8 9
> >
> > to A1:C3
> >
> > I'd thought I'd done something like Range("A1:C3") =
> > (1,2,3;4,5,6;7,8,9)
> > using ; as a delimiter between rows in the array, but that clearly
> > isn't working.
> >
> > I'm quite familiar with the looping/iterator techniques available to
> > assign a variable array to a range. I was just hoping there was a way
> > to do this in one line of code.
> >
>
>
>

Thanks guys, I'll need to read up on evaluate to understand what its
capable of altogether.

In hindsight, I believe I was remembering about creating 2D arrays in
formulas in spreadsheets, using the format shown within the quotation
marks.

JMB wrote:
> I always overlook the the opportunities to use Evaluate. Thanks Tom.
>
> "Tom Ogilvy" wrote:
>
> > Sub AABB()
> > Range("A1:C3").Value = _
> > Evaluate("{1,2,3;4,5,6;7,8,9}")
> >
> > End Sub
> >
> > works for small amounts of data.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "dodgo" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >I thought I'd done this in the past, but I can't remember if or how. I
> > > want be able to assign an array of values to a range in 1 go, such as
> > >
> > > 1 2 3
> > > 4 5 6
> > > 7 8 9
> > >
> > > to A1:C3
> > >
> > > I'd thought I'd done something like Range("A1:C3") =
> > > (1,2,3;4,5,6;7,8,9)
> > > using ; as a delimiter between rows in the array, but that clearly
> > > isn't working.
> > >
> > > I'm quite familiar with the looping/iterator techniques available to
> > > assign a variable array to a range. I was just hoping there was a way
> > > to do this in one line of code.
> > >
> >
> >
> >