need to put union of two ranges into 2D array

[cg85cg]

A program that I am writing requires that i put two different columns into a two dimensional array. the code line that gives the problem is:

daydata = Application.Union(Range("a1:a" & dayrows), Range(datatype$ & "1:" & datatype$ & dayrows))

The array has been previously ReDimed as daydata(dayrows, 2) and Option Base 1 is true

When the problem line runs it assigns the first range just fine, but changes the array into a one dimensional array.

dayrows = 1940
datatype$ = "f"

I can't really find anything online. Any help would be great. Thanks.

If more code is necessary i can provide it.

 

[SkipVought]

Hi,

Just loop thru each separate range and stuff in the appropriate array element.

Skip,

_[red][/red]

 

[cg85cg]

i'm just afraid that would take too long. it takes about 12 seconds for VBA to loop through 3000 cells. it will do this 252 times per year and for 7 years for a total of 5.88 hours. I need to write faster code. I need to take the entire range and put it in. i know i can do it with a 1D range into a 1D array. perhaps there is a way to take one range at a time and specifiy which column in the array it should go into. if anybody knows of a page that describes array techniques that'd be great.

 

[Bong]

union is going to return a range object. That is, your statement: daydata = Application.Union(Range("a1:a" & dayrows), Range(datatype$ & "1:" & datatype$ & dayrows)) is going to recast daydata as a range. I think you need to replan the whole concept. It seems you want an array where one column is range-1 and the other column is range-2. Is that right? Why? Ranges are already collections. You can do a lot of the same things with collections that you do with arrays.

_________________
Bob Rashkin

 

[stevexff]

As I've already stated on the other thread where you posted the same question: Is Excel really the right tool to be processing 5M+ rows of data? You'd be better off putting the rows on a database and running queries against it. From Excel, if you like.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[cg85cg]

steve, i absolutely agree with you. a database would be better for what i am doing. I chose to use excel because the data i am working with comes to me in excel files and i am fairly comfortable with excel.

An older version of the code i am writing would have taken something like 77 days to run. In other programs, i've used arrays to cut down the time by orders of magnitude. Even though excel may be less efficient than access, i feel a slower program now is better than a faster program later after i spend time learning access.

I think the best solution to my problem is to transfer the ranges to two different arrays and then combine the two 1D arrays into one 2D array.

 

[TonyJollans]

I don't know the answer to your question or the best way to address your real problem - some timings and trial and error on your part may be needed, but ..

.. you do not get a 1D array. You get a 2D array of the first Area in the Range created by the Union. The result of the Union is not necessarily a simple array and there really isn't much else that Excel/VBA can do.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[stevexff]

cg85cg

Given the 64K row limit in Excel, I guess you have one ticker file per day, about 3250 files in all. In which case a good part of your 5 hours is going to go in opening, loading, and closing files.

Once you've got the data in your array, what do you do with it? I mean, with the amount of historical data you have, surely it would make sense to summarise it into hourly, daily, weekly, monthly etc. totals that would make your analysis simpler and quicker? At least then you'd only have to grind through the full data set once, and then run a daily job to summarise the current day and apply it to your summary sheets.

This is the principle on which data warehouses are built, and it seems to work for them...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[CBasicAsslember]

I think the best solution to my problem is to transfer the ranges to two different arrays and then combine the two 1D arrays into one 2D array.

It is not pretty but it will put both columns in a 2 dimensional range.

Sub test()
Dim rCol1
Dim rCol2
Dim rColAll

' Get the columns
rCol1 = Sheet1.Range("A1:A32000")
rCol2 = Sheet1.Range("C1:C32000")

' Put the columns into a temp worksheet
Sheet3.Range("A1:A32000") = rCol1
Sheet3.Range("B1:B32000") = rCol2

' Get both columns in a 2 dim range
rColAll = Sheet3.Range("A1:B32000")

End Sub