Evaluating an INDEX / MATCH array formula in VBA (XL2007)

[AOB]

Hi guys,

So I've managed (with much valued help from steve the fish and others!!) to construct an INDEX / MATCH formula which works in a sheet (previous thread here...)

However, I want to evaluate the formula in VBA (partly because I need to use variables as the various criteria and partly because I need to be able to adequately handle the output where the formula returns nothing)

This is the formula as it looks in the sheet (this works)

{=IFERROR(INDEX(rngLookup,
  MATCH(1,
    (OFFSET(rngLookup,0,0,,1)="ABCD")*
    (ISNUMBER(SEARCH("Customer",OFFSET(rngLookup,0,1,,1))))*
    (OFFSET(rngLookup,0,2,,1)="EUR")*
    (OFFSET(rngLookup,0,4,,1)="D")
  ,0)
,6),"")}

This is my attempt at the VBA-constructed formula :

strFormula = "IFERROR(INDEX(rngLookup," & _
               "MATCH(1," & _
               "(OFFSET(rngLookup,0,0,,1)=" & Chr(34) & strCriteria1 & Chr(34) & ")*" & _
               "(ISNUMBER(SEARCH(" & Chr(34) & strCriteria2 & Chr(34) & ",OFFSET(rngLookup,0,1,,1))))*" & _
               "(OFFSET(rngLookup,0,2,,1)=" & Chr(34) & strCriteria3 & Chr(34) & ")*" & _
               "(OFFSET(rngLookup,0,4,,1)=" & Chr(34) & strCriteria4 & Chr(34) & "),0)," & lngReturnCol & ")," & Chr(34) & Chr(34) & ")"

However, when I try to evaluate this within VBA, I get a ZLS :

varTemp = Evaluate(strFormula)

But if I copy and paste strFormula into the sheet, it calculates fine?

Any suggestions?

Thanks!

AOB

 

[RoryA]

Remove the IFERROR (you can test the value of the variant instead). You'd get that if your formula errors - using this works for me:

    strFormula = "INDEX(rngLookup," & _
               "MATCH(1," & _
               "(OFFSET(rngLookup,0,0,,1)=""" & strCriteria1 & """)*" & _
               "(ISNUMBER(SEARCH(""" & strCriteria2 & """,OFFSET(rngLookup,0,1,,1))))*" & _
               "(OFFSET(rngLookup,0,2,,1)=""" & strCriteria3 & """)*" & _
               "(OFFSET(rngLookup,0,4,,1)=""" & strCriteria4 & """),0)," & lngReturnCol & ")"

assuming a range named rngLookup.

 

[AOB]

Thanks Rory

Removing the IFERROR, varTemp is returning Error 2029 (which I believe is the equivalent of #NAME? in a cell?)

Suggesting, to me anyway, that the evaluation doesn't recognise the named range ("rngLookup")

But the named range does exist (if I copy and paste the exact same formula, using the same named range, into a cell in the workbook, and Ctrl+Alt+Enter, it calculates correctly?)

 

[AOB]

Aaaargh! Solved...

I have another workbook open and it happens to be the active workbook at the time of the function call (and thus at the time the evaluation is made)

It works if I do this...

[COLOR=#FF0000]Dim wbk As Workbook
Set wbk = ActiveWorkbook
ThisWorkbook.Activate[/COLOR]
...
strFormula = "INDEX(rngLookup," & _
               "MATCH(1," & _
               "(OFFSET(rngLookup,0,0,,1)=" & Chr(34) & strCriteria1 & Chr(34) & ")*" & _
               "(ISNUMBER(SEARCH(" & Chr(34) & strCriteria2 & Chr(34) & ",OFFSET(rngLookup,0,1,,1))))*" & _
               "(OFFSET(rngLookup,0,2,,1)=" & Chr(34) & strCriteria3 & Chr(34) & ")*" & _
               "(OFFSET(rngLookup,0,4,,1)=" & Chr(34) & strCriteria4 & Chr(34) & "),0)," & lngReturnCol & ")"

varTemp = Evaluate(strFormula)
....
[COLOR=#FF0000]wbk.Activate
Set wbk = Nothing[/COLOR]

 

[RoryA]

You don't need to activate anything - just use the Evaluate method of a sheet in your workbook:

varTemp = ThisWorkbook.Worksheets(1).Evaluate(strFormula)

 

[AOB]

That's even better - love it!

Thanks Rory, that's really helpful!

 

[AOB]

Hi Rory / All,

I've just discovered that the Evaluate function is limited to 255 characters??

So in certain situations (where one of the strCriteriaX variables is longer than usual), I get Error 2015?

(Even though the formula works perfectly fine when pasted directly into a cell)

Is there any workaround??

Thanks!

Al

 

[steve the fish]

There is but its a right pain. Maybe its better if Rory explains. Basically involves replacing parts of the formula with variables whilst always keeping the formula a valid formula.

 

[AOB]

Urgh I've done that before tring to use .FormulaArray - painful

But at least with that, you set up the .FormulaArray with placeholders, then substitute additional strings for each placeholder?

With this, I can only .Evaluate once - how do I substitute further strings into it once it's been called?

 

[steve the fish]

I just thought of a workaround but its a bit long winded. You could replace all instances of your long criteria in the workbook with an obscure short criteria that would never normally appear (@@@@ for example). Evaluate your formula then replace them all back again.