Solved

Paste 2x2 array

Posted on 2011-03-21
4
387 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now