+ Reply to Thread
Results 1 to 8 of 8

VBA: Vlookup / Match on Array in memory

  1. #1
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,374

    VBA: Vlookup / Match on Array in memory

    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

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: VBA: Vlookup / Match on Array in memory

    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.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,827

    Re: VBA: Vlookup / Match on Array in memory

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,374

    Re: VBA: Vlookup / Match on Array in memory

    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.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,146

    Re: VBA: Vlookup / Match on Array in memory

    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

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,827

    Re: VBA: Vlookup / Match on Array in memory

    ...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.

  7. #7
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,374

    Re: VBA: Vlookup / Match on Array in memory

    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!

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,827

    Re: VBA: Vlookup / Match on Array in memory

    ...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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. use Match value to modify my Vlookup Array
    By JonLanguasco in forum Excel General
    Replies: 3
    Last Post: 09-15-2015, 09:20 AM
  2. [SOLVED] Can you set array for Vlookup or Index/Match using Offset?
    By inincubus in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2014, 12:52 PM
  3. Vlookup or index-match uses more memory?
    By Dwexdwex in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2013, 09:30 AM
  4. index, match, vlookup, array ....combining all these?
    By hog77 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2012, 12:29 PM
  5. Index/Match and VLookup "Syntax Memory Cards"
    By clayton8424 in forum Excel General
    Replies: 1
    Last Post: 06-11-2011, 02:21 PM
  6. Match look up Array from Vlookup.
    By jrodri14 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2010, 12:43 AM
  7. Match / Vlookup within an Array formula
    By Hari Prasadh in forum Excel General
    Replies: 3
    Last Post: 02-03-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1