Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to write a single dimension array into an excel column quickly using VBA

Posted on 2013-06-04
4
Medium Priority
?
389 Views
Last Modified: 2013-06-05
Hi Experts, I think this an easy one, but I'm having trouble figuring it out... I have a one dimension array with about 500,000 values that I need to dump into an excel column quickly.  I'm currently using the code:
For x = 1 To UBound(N_Data)
        Cells(x + 1, Last_Col) = N_Data(x)
Next x

Open in new window

Unfortunately this is very slow.  When I try to simply select the range and set the value equal to my data i.e.:
Range(Cells(2,Last_Col),Cells(Ubound(N_Data)+2),Last_Col)) = N_Data

Open in new window

it dumps blank values.  I think this is because the values in my array need to be transposed...
0
Comment
Question by:JamesCbury
  • 2
4 Comments
 
LVL 15

Assisted Solution

by:ChloesDad
ChloesDad earned 400 total points
ID: 39221234
You need to transpose the array

Dim xdata(9) As Integer

xdata(5) = 5

Range("a1:a10") = Application.Transpose(xdata)

puts a column of zeroes with row 6 = 5 (array is zero based)
0
 

Author Comment

by:JamesCbury
ID: 39222153
Thanks, that seems like it should work, but when I try to apply it I keep getting a Run time error "13" Type Mismatch...  any thoughts?

Range(Cells(2, Last_Col), Cells(UBound(N_Data) + 1, Last_Col)) = Application.Transpose(N_Data)

Open in new window

0
 
LVL 46

Accepted Solution

by:
aikimark earned 1200 total points
ID: 39222318
See my Fast Data Push to Excel article:
http://www.experts-exchange.com/A_2253.html

You need to create a 2D array, not a 1D (vector) data structure.
0
 

Author Comment

by:JamesCbury
ID: 39222669
Perfect - that did the trick.  I added a dimension to my array
ReDim N_Data(UBound(R_Data), 1)

Open in new window

Loaded my data into the "0" dimension (starting in the "0" position)
For x = 1 To UBound(R_Data, 1)
     N_Data(x - 1, 0) = 'insert calculated value here
Next x

Open in new window

Then bulk-wrote the results
Last_Col = Cells(1, 100).End(xlToLeft).Column
Range(Cells(2, Last_Col), Cells(UBound(N_Data, 1) + 1, Last_Col)) = N_Data

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

886 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