More on N-by-M assignment into a Worksheet

[gimme_this_gimme_that]

I'd like to get this code to enter "2,4,7" in A1:C10.

A requirement is that their be only a single assignment into the
worksheet.

DON{T POST A SOLUTION WHERE "2,4,7" IS ENTERED INTO THE WORKSHEET ON
EACH ITERATION.


If this example can be made to work the next step would be to time and
compare against similar code that assigns a row at a time and then a
cell at a time.

Thanks.

Sub N_By_M()
Dim x() As Variant, i As Long, startTime As Single, endTime As
Single
Dim b As Workbook
Dim s As Worksheet
Dim r As Range

ReDim x(10)
Const maxLoop As Long = 10

startTime = Timer

For i = 1 To maxLoop
ReDim Preserve x(1 To i)
x(i) = Array(2, 4, 7)
Next i

Set b = ActiveWorkbook
Set s = b.Sheets("Sheet1")
Set r = s.Range("A1:C10")
r.Value = Application.Transpose(x)

endTime = Timer
MsgBox endTime - startTime
End Sub

 

[NickHK]

You need to either assign the LBound and UBound of you arrays or be more
consistent in your apparoach. Maybe you have Option Base 1 set, but I do
not, hence:

ReDim x(10) '0 To 9 so you cannot then change to
ReDim Preserve x(1 To i)

Also, you have already dimmed x, so there is no reason to redim the loop.

So x is an array of variants, each element containing a Variant array.
Note an "array of variants" is not the same thing as "a Variant array". You
do not have a 2-D array. Try the line of code below:
?ubound(x,2)

However, can you not achieve the same with one line ?
Range("A1:C10").Value = Array(2, 4, 7)

NickHK

 

[gimme_this_gimme_that]

Also, you have already dimmed x, so there is no reason to redim the loop.

I'm rediming because I want to modify the example to dynamically build
the array and x will not be dimmed. So I'll take out the dim.

So x is an array of variants, each element containing a Variant array.
Note an "array of variants" is not the same thing as "a Variant array". You
do not have a 2-D array. Try the line of code below:
?ubound(x,2)

However, can you not achieve the same with one line ?
Range("A1:C10").Value = Array(2, 4, 7)

Well you could but then you'd destroy the significance of the example.
The point is to get N by M assignment into a worksheet going. The next
step after getting the syntax correct is to swap out the hardcoded
values of 2,4,7 with values read from a file or fetched from a SQL
resultset.

 

[NickHK]

You mean like this:

Dim arr() As Variant
Dim i As Long

For i = 1 To 5
ReDim Preserve arr(1 To i)
arr(i) = Array(1, 2, 3)
Next

Range("A1:C5").Value = Application.Transpose(Application.Transpose(arr))

End Sub

NickHK

 

[gimme_this_gimme_that]

Absolutely perfect NickHK!

Thanks!

 

[NickHK]

It was Tom who pointed out that Transpose flattens an array, so it was just
applying that really.

NickHK