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

Hi
I am just adding a short addition / update to this Thread, the subject of which continues to give me headaches.
In another thread I once again occurred the strange phenomena/ Bug in Match which is the subject of this Thread. In the particular problem I had , I tried by chance an Evaluate(“ Match ____ “) alternative, and the problem at first glance appeared to be gone in that case.. ( https://www.excelforum.com/developme...ml#post4671944 )

So I thought I should do a quick check on the codes and test data files used in this Thread.
I did some simple mods to one of the codes tested in this Thread , ( Sub USDAToDBArraysThirdWB18RngObjects() ) .
This was the basic idea , replacing the Application.Match lines with Evaluate(“ Match ____ “ )

         Let MtchHed = wsDBlx.Evaluate("=Match(""" & srchHed & """,[DBSept2016.xlsx]Tabelle1!$K$1:$BOW$1,0)")
         'Let MtchHed = Application.Evaluate("=Match(""" & srchHed & """,[DBSept2016.xlsx]Tabelle1!$K$1:$BOW$1,0)")
         'Let MtchHed = Application.Match(srchHed, rnglongSrcRow1, 0) 'Indicie "along"  of srchHed  ,  in   rnglongSrcRow1,  exact match stipulated ( 0 )

I tested the modified , ( Sub USDAToDBArraysThirdWB18RngObjectsEval() ) , code just on one system used in the previous measurements in this Thread (Acer Aspire 4810TZG 32Bit 4 GB RAM, XL 2007 32Bit )
_ Application Evaluate code bombed out at about 172 rows after about 38 seconds
_ Worksheets Evaluate code bombed out at about 355 rows after about 42 seconds.

The code which I modified for the measurements, ( Sub USDAToDBArraysThirdWB18RngObjects() ) , is bombing out today on my computer at about [size=3][b]350 rows after 36 seconds, which is similar to previous measurements.

_....................

So, Poo ! , that was no good either…

_.. I am quite frustrated at the little “quirks” in Worksheet Functions that I am finding. I did have some improvements in speed sometimes, over my preferred simple VBA Array manipulation codes, for things like Match for a long LookUp range with minimal data matches. But some of these little hidden quirks and bugs I am finding when you do something a bit more demanding in terms of size are making inclined to live with occasional slower performance but at least have a better idea and control of what is going on….
Possibly it is just down to the routines behind the functions not being written quite as well as they should have been….
Oh well…
Never mind.
Alan

_......................
I updated the File with macros in it to include the new code:
“MacrosOnly.xlsm” https://app.box.com/s/w4lz07w54l8twtuj2qwc8jg277tpeph1

Data file to be filled in with sorted data:
“DBSept2016.xlsx” https://app.box.com/s/enugbnro26tfduoehme7cdjvgwpf2ct5

Raw data file ( irrational rational database file ):
“NutritionalValues2016.xlsx” https://app.box.com/s/6i52eis914evpabl52gh3fr9jz4bdlbx







https://fastexcel.wordpress.com/2011.../#comment-4175