Excel 2013 VBA - Executing the INDEX/MATCH alternative to VLOOKUP

[ntm91307]

Hello all;

I am a fairly experienced VBA programmer but this is just kicking my backside. I'm trying to lookup a single row in a sheet/table to return a single value based on the values of 5 variables (Yep, it takes 5 pieces of data to isolate a unique row and yes I've preached DATABASE for this project from the very beginning but have been shot down so....).

I can use the =INDEX(....MATCH(1 ,.....,....),0) method in a cell and get an answer. For those of you unfamiliar this rendition of MATCH it uses an array formula as the second argument and returns the product of a number of comparisons (eg. when it finds a row in the table that matches all 5 variables it matches the number 1 that we're looking for and returns the row which is then fed into INDEX. The problem is this now becomes an array formula and I can't get it to execute in VBA. All my attempts either fail to compile or when executed gets me a Type Mismatch error because of the array formula.

I don't want to leave that formula in a cell. This is a massive sheet in an even more massive workbook and recalculating 10,000+ formulas every time you change a single cell value takes forever. I'm using VBA code to determine the correct values and just plopping them in their cells. These values change maybe twice a year so they can just run the code when new data comes in and then get on with their work when it's done running. Alas, nothing I've tried works.

I've come down to breaking this down and putting my MATCH call into a string variable and using the EVALUATE function in VBA. The idea was to get the row number separately and then execute an INDEX function but I can't get past MATCH. The code snippet is as follows:

strEvaluate = "Match(1, (rngYear = intYr) * (rngTech = strTech) * (rngOldModel = arrSchedulerRoles(3, z)) * (rngReplace = strReplace) , 0)"

If Not IsError(varPos = Application.Evaluate(strEvaluate)) Then .....

The rng variables are, as their prefix indicates, Range objects set on the columns of the table we're trying to match to. Each criteria will return a 0 or 1 depending on whether it's true or false. Those criteria are multiplied together to produce an ultimate 0 or 1 product for all criteria. The row that comes back with a 1 for all criteria is the one I'm looking for and is passed back by MATCH. Like I said, the trouble is the Array Formula is causing a Mismatch error.

Has anybody done this before in VBA? If so, I could use some help.

Thank you in advance one and all.....

 

[Domenic]

Hi, and welcome to the Board!

Assuming that the sheet containing the lookup data is the active sheet, try...

    varPos = Application.Evaluate("Match(1,(" & rngYear.Address & "=" & intYr & ")*(" & rngTech.Address & "=""" & strTech & """)*(" & rngOldModel.Address & "=""" & arrSchedulerRoles(3,z) & """)*(" & rngReplace.Address & "=""" & strReplace & """),0)")
    
    If Not IsError(varPos) Then . . .

If the sheet containing the lookup data is not the active sheet, try...

    varPos = Application.Evaluate("Match(1,(" & rngYear.Address(, , , True) & "=" & intYr & ")*(" & rngTech.Address(, , , True) & "=""" & strTech & """)*(" & rngOldModel.Address(, , , True) & "=""" & arrSchedulerRoles(3,z) & """)*(" & rngReplace.Address(, , , True) & "=""" & strReplace & """),0)")
    
    If Not IsError(varPos) Then

Note that the Evaluate method cannot accept a string whose length exceeds 255 characters.

Also, note that if the criteria is a string, it needs to be wrapped within quotes, hence the use of double quotes. I've assumed that all criteria, except for the year, are strings.

Hope this helps!

 

[ntm91307]

Thank you for your assistance. Your code executes flawlessly the first time...

Unfortunately it comes back with Error 2015 - Data Type Mismatch. There's one range where the cells can be either a numeric or alphanumeric and Excel is kind/unkind enough to differentiate this at the cell level in both Ranges and arrays despite having set the format of those cells in the sheet to Text in advance. So I forced all of these cell and array elements to text in my code. Then I looked at each element in the other ranges and arrays and at each variable I'm comparing everything to and find no type mismatch. I even tried using a DOUBLE variable instead of an integer for the Year comparison just in case comparing integer to double (what Excel has decided my Year column to be) had some quirk in it.

There is no joy in Mudville today....Mighty Mike has struck out.

Any other thoughts? Anybody?

Thank you....

 

[Domenic]

Try...

    Dim vSchedulerRole As Variant
    
    vSchedulerRole = arrSchedulerRoles(3, Z)
    
    Select Case TypeName(vSchedulerRole)
        Case "String"
            varPos = Application.Evaluate("Match(1,(" & rngYear.Address & "=" & intYr & ")*(" & rngTech.Address & "=""" & strTech & """)*(" & rngOldModel.Address & "=""" & vSchedulerRole & """)*(" & rngReplace.Address & "=""" & strReplace & """),0)")
        Case "Date"
            varPos = Application.Evaluate("Match(1,(" & rngYear.Address & "=" & intYr & ")*(" & rngTech.Address & "=""" & strTech & """)*(" & rngOldModel.Address & "=" & CLng(vSchedulerRole) & ")*(" & rngReplace.Address & "=""" & strReplace & """),0)")
        Case Else
            varPos = Application.Evaluate("Match(1,(" & rngYear.Address & "=" & intYr & ")*(" & rngTech.Address & "=""" & strTech & """)*(" & rngOldModel.Address & "=" & CDbl(vSchedulerRole) & ")*(" & rngReplace.Address & "=""" & strReplace & """),0)")
    End Select

Hope this helps!

 

[ntm91307]

Unfortunately no, but I've worked around it for now using a combination of filtering a sheet on multiple criteria, copying the results to a TempWorksheet and performing Range.Find on that sheet for the rest. A non-elegant solution to be sure but effective and the customer (a rather large one) is screaming for this so we bite the bulllet and move on.

Thanks for trying....

 

[Domenic]

Do you still want to try and resolve it as a learning exercise?

If not, no problem.

If so, what happens exactly? Did you get an error? If so, which one? And what value is assigned to "vSchedulerRole" when the error occurs?

 

[ntm91307]

Ultimately I end up with the same Type Mismatch Error which leads me to believe we've just found three different ways to get to the same place...

I know if I entered this same formula in a cell I have to hit Ctrl-Shift-Enter to properly activate it. I can't seem to find the equivalent in VBA but I suspect that's the path we'd need to go down.

If that triggers anything for you I'm up for the learning experience. This issue has caused me to go from a single line of code to get a single answer to about 15 lines to get the same answer and that doesn't sit well.

By all means let's continue the conversation....

 

[Domenic]

Good, let's continue.

What type of data does your sheet contain? Can you provide a small sample of the data?

When the error occurs and you click on "Debug", which line of code gets highlighted?

When the error occurs, what values are assigned to your variables?