Assign a 2D array of constant values to a range
 

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.

Assign a 2D array of constant values to a range
 

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.

Assign a 2D array of constant values to a range
 

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" wrote in message
oups.com...
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.

Assign a 2D array of constant values to a range
 

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" wrote in message
oups.com...
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.

Assign a 2D array of constant values to a range
 

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" wrote in message
oups.com...
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.