More on N-by-M assignment into a Worksheet

  • Thread starter gimme_this_gimme_that
  • 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
 
N

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
 
G

gimme_this_gimme_that

NickHK said:
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.
 
N

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
 
N

NickHK

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

NickHK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top