Type Mismatch Evaluating An Array Formula

    Hey gang. I'm completely at a loss here. I cannot see anything wrong with what I'm doing unless Evaluate() can't handle nested ifs in an array, but I'm sure I've evaluated nested ifs in an array formula before.


    I have a table:
    [TABLE="width: 1189"]

    [tr]


    [TD="align: center"]Ontology ID[/TD]
    [TD="align: center"]Attribute Order[/TD]
    [TD="align: center"]Ontology[/TD]
    [TD="align: center"]Attribute ID[/TD]
    [TD="align: center"]Attribute[/TD]
    [TD="align: center"]Mandatory[/TD]
    [TD="align: center"]Attribute Type[/TD]

    [/tr]


    [tr]


    [TD="align: center"]166[/TD]
    [TD="align: center"]1[/TD]
    [TD="align: center"]Lighting - Stop, Tail & Combination Lights[/TD]
    [TD="align: center"]800[/TD]
    [TD="align: center"]Light Type[/TD]
    [TD="align: center"]Mandatory[/TD]
    [TD="align: center"]VARCHAR2[/TD]

    [/tr]


    [tr]


    [TD="align: center"]166[/TD]
    [TD="align: center"]2[/TD]
    [TD="align: center"]Lighting - Stop, Tail & Combination Lights[/TD]
    [TD="align: center"]795[/TD]
    [TD="align: center"]Light Style[/TD]
    [TD="align: center"]Mandatory[/TD]
    [TD="align: center"]VARCHAR2[/TD]

    [/tr]


    [tr]


    [TD="align: center"]166[/TD]
    [TD="align: center"]3[/TD]
    [TD="align: center"]Lighting - Stop, Tail & Combination Lights[/TD]
    [TD="align: center"]1395[/TD]
    [TD="align: center"]Size[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]VARCHAR2[/TD]

    [/tr]


    [tr]


    [TD="align: center"]166[/TD]
    [TD="align: center"]3[/TD]
    [TD="align: center"]Lighting - Stop, Tail & Combination Lights[/TD]
    [TD="align: center"]1625[/TD]
    [TD="align: center"]Voltage[/TD]
    [TD="align: center"]Mandatory[/TD]
    [TD="align: center"]VARCHAR2[/TD]

    [/tr]


    [tr]


    [TD="align: center"]167[/TD]
    [TD="align: center"]1[/TD]
    [TD="align: center"]Lighting - Strip Lighting[/TD]
    [TD="align: center"]1680[/TD]
    [TD="align: center"]Item Type[/TD]
    [TD="align: center"]Mandatory[/TD]
    [TD="align: center"]VARCHAR2[/TD]

    [/tr]


    [tr]


    [TD="align: center"]167[/TD]
    [TD="align: center"]2[/TD]
    [TD="align: center"]Lighting - Strip Lighting[/TD]
    [TD="align: center"]1625[/TD]
    [TD="align: center"]Voltage[/TD]
    [TD="align: center"]Mandatory[/TD]
    [TD="align: center"]VARCHAR2[/TD]

    [/tr]


    [tr]


    [TD="align: center"]167[/TD]
    [TD="align: center"]3[/TD]
    [TD="align: center"]Lighting - Strip Lighting[/TD]
    [TD="align: center"]1695[/TD]
    [TD="align: center"]Mount Type[/TD]
    [TD="align: center"]Mandatory[/TD]
    [TD="align: center"]VARCHAR2[/TD]

    [/tr]


    [tr]


    [TD="align: center"]167[/TD]
    [TD="align: center"]4[/TD]
    [TD="align: center"]Lighting - Strip Lighting[/TD]
    [TD="align: center"]350[/TD]
    [TD="align: center"]Colour[/TD]
    [TD="align: center"]Mandatory[/TD]
    [TD="align: center"]VARCHAR2[/TD]

    [/tr]


    [tr]


    [TD="align: center"]167[/TD]
    [TD="align: center"]5[/TD]
    [TD="align: center"]Lighting - Strip Lighting[/TD]
    [TD="align: center"]775[/TD]
    [TD="align: center"]Length (mm)[/TD]
    [TD="align: center"]Mandatory[/TD]
    [TD="align: center"]DOUBLE[/TD]

    [/tr]


    [/TABLE]


    I need to loop through attribute names (column 5) for an ontology (column 3) if the attribute is mandatory (column 6).


    In the worksheet, my array formula works perfectly: =OFFSET(E1,MATCH(SMALL(IF(C2:C888="Lighting - Stop, Tail & Combination Lights",IF(F2:F888="Mandatory",D2:D888,FALSE)),1),D2:D888,0)*1,0) yields a result of "Light Style".


    If I translate this to VBA, I get a type mismatch error. Here is my code:


    Code
    For x = 1 To NumMandatoryAttributes
    CurrentAttribute = Evaluate("=OFFSET(Attributes!E1,MATCH(SMALL(IF(Attributes!C2:C" & AttributesFinalRow & "=""" & Target & """,IF(F2:F" & AttributesFinalRow & "=""Mandatory"",D2:D" & AttributesFinalRow & ",FALSE))," & y & "),Attributes!D2:D" & AttributesFinalRow & ",0)*1,0)")
    y = y + 1
   Next x


    My variables are all assigned correctly:
    NumMandatoryAttributes = 3
    AttributesFinalRow = 888
    Target = Lighting - Stop, Tail & Combination Lights
    y = 1


    I know you can't evaluate something longer than 255 characters, but this is 247.


    I've been busting my head over this for two hours. Can anyone help me shed some light on this? Cheers!

    Re: Type Mismatch Evaluating An Array Formula


    When you enter all the variable values into the formula, how long is it? I think that is what has to be 255 or less, but not 100% sure on that.

    Re: Type Mismatch Evaluating An Array Formula


    The variable values actually make it smaller. I think it comes out around 170 characters.

    Re: Type Mismatch Evaluating An Array Formula


    Figured so, but it was a thought....here's another one. Again, I'm just shooting in the wind here, but this part:


    IF(F2:F" & AttributesFinalRow & "=""Mandatory"",D2:D"


    Looks like it's missing some & characters...like it should be


    IF(F2:F" & AttributesFinalRow & "=" &"Mandatory" &",D2:D"


    but like i said, not sure. Type mismatch sounds like a variable typo but I didn't see any, and assuming you're using Option Expicit that would have bugged out and highlighted right away.



    EDIT: Nevermind...I see what ya got now that I moved it over to a module.

    Re: Type Mismatch Evaluating An Array Formula


    I appreciate the help. Yes, I'm using option explicit. I don't think I could live without it at this point.


    I tried adding in the '&' characters and get the same error. Geez this is really irritating.

    Re: Type Mismatch Evaluating An Array Formula


    Yea, the & wasn't it. Without the entire code and workbook it's hard to say. Is it possible to post the workbook or is it secretive type data?

    Re: Type Mismatch Evaluating An Array Formula


    This biggest problem is that it's far too complicated as a whole to ask anyone to go through what I've done. It's tons and tons of code and there's no way I would subject anyone to going through it. Maybe I can use the autofilter instead of looping...

    Re: Type Mismatch Evaluating An Array Formula


    Let me try to simplify this. Can anyone see an issue with this piece of code that would cause a type mismatch?


    Code
    CurrentAttribute = Evaluate("=OFFSET(Attributes!E1,MATCH(SMALL(IF(Attributes!C2:C888=""Lighting - Stop, Tail & Combination Lights"",IF(F2:F888=""Mandatory"",D2:D888,FALSE)),1),Attributes!D2:D888,0)*1,0)")


    That formula works in the worksheet, but not in VBA and it is well under the 255 character limit.

    Re: Type Mismatch Evaluating An Array Formula


    CurrentAttribute is currently defined as a string. Also using option explicit to try to catch anything else going on. I have tried changing it to a variant. The only difference is that the statement evaluates to an error rather than getting a debug message.

    Re: Type Mismatch Evaluating An Array Formula


    Quote from Smallman;602884

    Hi


    Get rid of the = sign. Not needed.


    Smallman


    irrelevant.


    jj


    Now you have a small progress.


    Evaluate method evalutes the range,if there are, in the active sheet unless it is specified.

    Re: Type Mismatch Evaluating An Array Formula


    jindon, I think I get what you mean, but I get the 'Type Mismatch' error regardless of which worksheet is active.

    Re: Type Mismatch Evaluating An Array Formula


    Again,


    Type mismatch error is raised when the variable type and the returned value are different type of data.


    So if the variable type is Variant, it accepts Error whereas String type can not.


    You will need to evaluate the formula again.

    Re: Type Mismatch Evaluating An Array Formula


    Sorry, I guess I didn't understand you the first time. Yes, I understand that. So when I change the variable to a variant, the statement evaluates to an error, but that's not correct because the exact same formula works perfectly when placed in a cell in the worksheet or any worksheet for that matter.

    Re: Type Mismatch Evaluating An Array Formula


    This just keeps getting weirder...


    I created a sample workbook with just my little table that I posted previously and a macro to evaluate the statement and it worked perfectly. It returned, "Light Style" just like you got. The statement is exactly the same in the workbook that is giving an error. The only difference is that it resides in a Worksheet_Change event rather than a macro. If Excel had a face I would have punched it by now.

    Re: Type Mismatch Evaluating An Array Formula


    Sigh... you were right and I was just too code-blinded to see that I missed a pair of sheet references in my statement:


    I can't believe it took me over a day to see this. Thanks for pointing me in the right direction, jindon. It is much appreciated.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login