More on N-by-M assignment into a Worksheet

  • Thread starter Thread starter gimme_this_gimme_that
  • Start date Start date
G

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
 
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)
Click to expand...

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
 
NickHK said:
Also, you have already dimmed x, so there is no reason to redim the loop.
Click to expand...

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)
Click to expand...
However, can you not achieve the same with one line ?
Range("A1:C10").Value = Array(2, 4, 7)
Click to expand...

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.
 
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
 
It was Tom who pointed out that Transpose flattens an array, so it was just
applying that really.

NickHK
 
Back
Top