Problem assigning values to a range of cells
 

The first line works fine to set the block of cells...
Why won't the 2nd line work? (results are all cells are blank, the "1"'s
all get wiped out)

Range("F3:H6").Value = "1"
Range("F3:H6").Value = Array(Array("1", "2", "3"), Array("4", "5", "6"),
Array("7", "8", "9"), Array("10", "11", "12"))

2nd line should all be on 1 line in the VBE; I also tried making the data
just integers... Array(1,2,3).... but that did the same thing.

What is the best/better way to assign a user-type module level multidim
array to a contiguous block of cells? Using a loop to scroll through the 2
dimensions and assigning each value to a cell (via range.offset.value) sure
seems slow (even with screen updating off and calculation off)

Is there a way to set up the definition of an array that can be directly
assigned to a range of cells?
I.E:
[F3:H6] = myarray
Where myarray matches the cell blocks in dimension.

--
Regards,
John

Problem assigning values to a range of cells
 

To assign an array to a range it has to be a 1-based 2-D array and if the
whole
array has to go to the whole range than the range and the array must have
the same number of rows and columns.
So for example this will work:

Sub test()

Dim i As Long
Dim c As Long
Dim n As Long
Dim arr(1 To 4, 1 To 3) As Long

For i = 1 To 4
For c = 1 To 3
n = n + 1
arr(i, c) = n
Next
Next

Range(Cells(3, 6), Cells(6, 8)) = arr

End Sub


RBS


"John Keith" wrote in message
...
The first line works fine to set the block of cells...
Why won't the 2nd line work? (results are all cells are blank, the "1"'s
all get wiped out)

Range("F3:H6").Value = "1"
Range("F3:H6").Value = Array(Array("1", "2", "3"), Array("4", "5", "6"),
Array("7", "8", "9"), Array("10", "11", "12"))

2nd line should all be on 1 line in the VBE; I also tried making the data
just integers... Array(1,2,3).... but that did the same thing.

What is the best/better way to assign a user-type module level multidim
array to a contiguous block of cells? Using a loop to scroll through the
2
dimensions and assigning each value to a cell (via range.offset.value)
sure
seems slow (even with screen updating off and calculation off)

Is there a way to set up the definition of an array that can be directly
assigned to a range of cells?
I.E:
[F3:H6] = myarray
Where myarray matches the cell blocks in dimension.

--
Regards,
John

Problem assigning values to a range of cells
 

RB Smissaert wrote:
To assign an array to a range it has to be a 1-based 2-D array . . . .

The array needs to be neither 1-based nor 2-D

Sub test()

Dim i As Long
Dim c As Long
Dim n As Long
Dim arr(0 To 3, 0 To 2) As Long

For i = 0 To 3
For c = 0 To 2
n = n + 1
arr(i, c) = n
Next
Next

Range(Cells(3, 6), Cells(6, 8)) = arr

End Sub

works, as does

Dim arr(0 to 3)
For i = 0 to 3
arr(i) = i
Next
Range("A1:C1").Value = arr

Alan Beban

Problem assigning values to a range of cells
 

Yes, you are right there.
I was thinking to make it easy for the particular example, but of course
people other than the OP will read it.

RBS

"Alan Beban" wrote in message
...
RB Smissaert wrote:
To assign an array to a range it has to be a 1-based 2-D array . . . .

The array needs to be neither 1-based nor 2-D

Sub test()

Dim i As Long
Dim c As Long
Dim n As Long
Dim arr(0 To 3, 0 To 2) As Long

For i = 0 To 3
For c = 0 To 2
n = n + 1
arr(i, c) = n
Next
Next

Range(Cells(3, 6), Cells(6, 8)) = arr

End Sub

works, as does

Dim arr(0 to 3)
For i = 0 to 3
arr(i) = i
Next
Range("A1:C1").Value = arr

Alan Beban