Big List Sort. Not Enough Memory. Help Run my Code. Or help fix or change code Please :)

By way of an update, I did some testing yesterday and I believe the memory issue lies in the application.match loop and its use of Range objects. The solution I think is to use an array of data and loop through it (match does not seem to like arrays with long text in them, which you have). It will be slower but use far less memory. My initial tests were around 30minutes but I have not verified the data as yet because I had made numerous other changes first.

Originally Posted by xlnitwit

By way of an update, I did some testing yesterday and I believe the memory issue lies in the application.match loop and its use of Range objects. The solution I think is to use an array of data and loop through it (match does not seem to like arrays with long text in them, which you have). It will be slower but use far less memory. My initial tests were around 30minutes but I have not verified the data as yet because I had made numerous other changes first.

Hi xlnitwit,
That is all very interesting, thanks
That sorting part of the code and the use Application.Match with a Range Object as second argument came out from this Thread
http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
The original reason for posting that Thread was that I had previously got in the habit of using Arrays in most sorting codes. But hen suddenly I then got caught out when I found that VBA .Match worked differently for second argument Array of Elements Variant Types or String Types for Strings of over 255 Characters!! ( To do a simple conversion of the Range Object to an Array using_.,.
Range.Value
_..would return me Variant Elements )

( Note the problem only occurs for Variant Element types, String Element types are fine ) That curiosity was never really explained, but_..

_..I then went on to make many numerous test, and found that the use of Application.Match with a Long Row Range Object as second argument was by far the most efficient. It also did not have the 255 String limit.
http://www.eileenslounge.com/viewtop...=22512#p175343


After that experience I changed my opinion slightly about Arrays_...
http://www.excelforum.com/showthread...95#post4380613
_... I still do favour them, but note that there are occasions when a Worksheet Function with a Range object works better. My gut feeling is that that is the case here

I cannot do a direct comparison here with a substitution of an Array through
Array() = Range.Value
as I am limited to the 255 column Limit when using Arrays in Worksheet Functions( I have a bit under 2000 )

What approach did you use in the test which took 30 minutes?

The codes I used for those speed test here
http://www.eileenslounge.com/viewtop...=22512#p175343
had an extra complexity, ( - The initial Heading String used as the search first argument in the .Match first argument ( my Nutrition Name , ) had also 20 variations to be gone through. In this example there is just the one in the data File and 20 Header spelling possibilities in the File to have the final sorted rows pasted in). Using a very simple Arrays only code in that previous work changes the time per row from 3.1 secs to 52secs!! - So it might still be a possibility here. 30 minutes may bot be too bad

What was approximately your time taken to produce the Full File you sent me ?

_.. It is very helpful you taking the time to look at this my resources are a bit tied down....

_...... As way of an update on

Originally Posted by Doc.AElstein

...
Second attempt at code to get data from Big Closed Workbook
_.... Second attempt at code to get data from Big Closed Workbook with the “_....................“perform the reference “ or “Get the reference” method or GetValue function way
GetReferrence = ExecuteExcel4Macro(FullReferrencecell)
Where GetReferrence , a String variable gets given the string of the held for the cell value..

Let GetValueOfClosedWBCell = ExecuteExcel4Macro(StrTemp)
.............................” ........idea !!...

_.. Day 3 : The first long column Array has not been filled yet ! ( In the spreadsheet interaction codes I still use an Array initially to get the Product Names from which to get a unique list with the MSR Dictionary )
( I assume the code is still going – I could put a _...
Application.DisplayStatusBar =
_...in it , - but I think that often increases the time as well – maybe not significantly here Lol.!
It may be time for me to stop that experiment ..)
_...


_.. So many possibilities to try.. a bit overwhelming!


Thanks again for you help.

Alan