Solved

Paste 2x2 array

Posted on 2011-03-21
4
389 Views
Last Modified: 2012-05-11
Hello-- I am retrieving data from a database using embedded sql code in VBA and storing it in a 2x2 array. I need to paste it all into spreadsheet and right now am just doing it with a nested for-loop:

For i = 0 To UBound(dbdata,2)
    For j = 0 To UBound(dbdata)
        ActiveSheet.Cells(i+1,j+1).Value = dbdata(j,i)
    Next
Next

But it is a tremendous array and it's a pretty long run-time to insert each value in the array into the spreadsheet one by one. Is there a way to just paste the entire array into the spreadsheet in one fowl swoop, ie something along the lines of dbdata.paste [with dbdata(1,1) at ActiveSheet.Cells(1,1)]?

Thanks.
0
Comment
Question by:Jeff9687
  • 3
4 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 35181939
Does this work?
ActiveSheet.Cells(1,1).resize(ubound(dbdata,1),ubound(dbdata,2)).Value=dbdata

Open in new window

0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35181992
You'll need to transpose the data into another array first, then use Stephen's code.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35181997
Or use CopyFromRecordset instead of GetRows which I guess is how you are getting the data into an array in the first place?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35182282
Without any more information, it would appear that a points split was in order here?
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question