VBA: Vlookup / Match on Array in memory

[JasperD]

Hi all,

I hope you're doing well! Quick question – I think I remember it not being possible, but perhaps someone can prove me wrong.

I use the following to set the usedrange of my worksheet as an array in memory:

Please Login or Register  to view this content.

So saying

Please Login or Register  to view this content.

would give me the value in Sheet1.Range(“A1”)

But what if I want to do an Application.Vlookup or Application.Match on the array?
Using it as below gives me an Error 2042.

Please Login or Register  to view this content.

Is it just not possible and should I loop or is there some way to apply vlookup/match (or find, for that matter) on the 'sht' ?
Thanks,

Jasper

[Olly]

Error 2042 is #NA - it tells you that your lookup value was not found. You just need to handle the error - here's one way:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

More often than not I find it useful to have check cells, i.e. cells that you want to use to test or count something on the sheet rather than create the test in VBA. I just find it easier to maintain systems if I have these sorts of things all together and readily visible.

But a MATCH function won't work with a 2 dimensional range anyway, you'd have to modify the UsedRange with a .Resize to make it a single column range

So in your example just put an =MATCH() function in a named cell and read the value of that named cell in your VBA code.

[JasperD]

Thanks a lot for the replies, Olly and Richard,

@Olly - the value is there, it's not "not found". No value would be found in a two-dimensional array, so the answer to me is still "can't be done on an array in memory".

@Richard - the reason why I don't do this is because of speed. Handling things on the sheet makes code a lot slower than doing everything in memory. I need to do 40k+ values over 10 columns in as little time as possible. I can do the match in VBA referring to the sheet, but that would always be slower than working in the memory.

Question then is: will looping through the array in memory as per below be quicker than do a match on the sheet?

Loop:

Please Login or Register  to view this content.

Match:

Please Login or Register  to view this content.

[Kyle123]

I reckon a formula on an in memory array is much slower than on a worksheet.

99/100 a simple loop will beat a formula on an array

[Richard Buttrey]

...Difficult to comment further without knowing all the code that's involved and I'm still puzzled at your code which if the UsedRange returns a 2 dimensional range, attempting to do a Match on such a range won't work.

[JasperD]

Thanks Kyle - I actually think you told me that once before. I'm using a loop now and will time the differences to see which is quicker

@Richard: exactly, it doesn't work. So I was trying to figure out if it had potential to work with some adjustments (which it doesn't) and if not, if looping or working on the worksheet would be quicker.
Right now I've opted for looping, but I'll time the both options later and reply here which is quicker :-)

Thanks for all your replies!

[Richard Buttrey]

...I still don't think we know what the end game is. All we know is that you want to use a particular function. Perhaps if you tell us in a narrative form what you are trying to do we may have other ideas. So assuming you do find matches for stuff what do you then want to do?

I'll concede this may not apply for you but I've lost count of the number of Qs we get here where the assumption that VBA and in particular loops are quicker are just wrong. Most times standard Excel functionality like filtering driven by a macro is far quicker. Unless you explain further then we'll never know and you may be missing out on a much more straightforward solution.