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"]
[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]
[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]
[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]
[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]
[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]
[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]
[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]
[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]
[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]
[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]
[/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:
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!