Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

I am totally confused with this one...

If I use this then its entering the formula in A1 cell and I have to press CTRL+SHIFT+ENTER to make it as array and its returning the right result.

Range("A1").Value = "=INDEX(" & rRes.Address(, , , True) & ",SMALL(IF((" _
    & rEmpNo.Address(, , , True) & "=" & vEmpNo.Address(, , , True) & ")*(" & rDate.Address(, , , True) & ">=" _
    & vStDate.Address(, , , True) & ")*(" & rDate.Address(, , , True) & "<=" & vEndDate.Address(, , , True) & "),ROW(" _
    & rDate.Address(, , , True) & ")-ROW(" & rDate.Cells(1).Address(, , , True) & ")+1),ROW(A1)))"

The result of the above formula in A1 cell is
=INDEX('Leave Credit'!$D$2:$D$8,SMALL(IF(('Leave Credit'!$B$2:$B$8='Leave card'!$D$10)*('Leave Credit'!$D$2:$D$8>='Leave card'!$C$8)*('Leave Credit'!$D$2:$D$8<='Leave card'!$F$8),ROW('Leave Credit'!$D$2:$D$8)-ROW('Leave Credit'!$D$2)+1),ROW(A1)))

After CTRL+SHIFT+ENTER it is returning the right results.

But when I use evaluate with this

Range("A1").Value = Evaluate("=INDEX(" & rRes.Address(, , , True) & ",SMALL(IF((" _
    & rEmpNo.Address(, , , True) & "=" & vEmpNo.Address(, , , True) & ")*(" & rDate.Address(, , , True) & ">=" _
    & vStDate.Address(, , , True) & ")*(" & rDate.Address(, , , True) & "<=" & vEndDate.Address(, , , True) & "),ROW(" _
    & rDate.Address(, , , True) & ")-ROW(" & rDate.Cells(1).Address(, , , True) & ")+1),ROW(A1)))")

It's returning #Value as result....

If I use it in this way

Range("A1").FormulaArray = "=INDEX(" & rRes.Address(, , , True) & ",SMALL(IF((" _
    & rEmpNo.Address(, , , True) & "=" & vEmpNo.Address(, , , True) & ")*(" & rDate.Address(, , , True) & ">=" _
    & vStDate.Address(, , , True) & ")*(" & rDate.Address(, , , True) & "<=" & vEndDate.Address(, , , True) & "),ROW(" _
    & rDate.Address(, , , True) & ")-ROW(" & rDate.Cells(1).Address(, , , True) & ")+1),ROW(A1)))"

Then it's returning...

Run-time error 1004
Unable to set the formulaArray property of the range class.

Formulaarray only works with R1C1 references. Your address properties will return A1 style references.

Edit: you might be able to get around this with:

Activecell.formula = "formula"
Activecell.formulaarray = activecell.formular1c1

but I don't know if it will work.

@ yudlugar,

Thanks for the suggestion and it's working fine by following the below way...

Range("A1").Formula = "MyFormula"
Range("A1").FormulaArray = Range("A1").FormulaR1C1

But now my issue is I am using this with an UDF, I just shown Range("A1") for easy understanding just worrying how I have to use it with an variable

Thanks for the suggestion but keeping the reference as R1C1 is not running the formula as array...

abc = "=INDEX(" & rRes.Address(, , xlR1C1, True) & ",SMALL(IF((" _
    & rEmpNo.Address(, , xlR1C1, True) & "=" & vEmpNo.Address(, , xlR1C1, True) & ")*(" & rDate.Address(, , xlR1C1, True) & ">=" _
    & vStDate.Address(, , xlR1C1, True) & ")*(" & rDate.Address(, , xlR1C1, True) & "<=" & vEndDate.Address(, , xlR1C1, True) & "),ROW(" _
    & rDate.Address(, , xlR1C1, True) & ")-ROW(" & rDate.Cells(1).Address(, , xlR1C1, True) & ")+1),ROW(A1)))"

Even I tried

Range("A1").Value = Evaluate("=" & abc)

But not converting as array getting #Value error...

Try this.

<range>.Address(ReferenceStyle:=xlR1C1)

Originally Posted by Norie

Try this.

<range>.Address(ReferenceStyle:=xlR1C1)

@ Norie,

Thanks for the suggestion

But in formula I want to have the sheet reference too.. I am unable to get the sheet reference other than marking the forth argument external as True, any other alternate suggestion for this one will be helpful since setting the external property as true getting the file name too...

I couldn't actually work out which argument it was, all those commas confused me.

Did you try adding External:=True?

@ Norie,

Yes, like I shown in Post #6, I turned on both R1C1 ref and external ref as true but not sure how I can convert this as array and assign the value to a variable

What I seen in the Address argument the third one is Reference Style and the Fourth argument is External reference and I made it in the same way in Post #6, but no luck

Why not use Named arguments?

It works for me.

This returns [Book1]Sheet1!R1C1:R10C1.

Range("A1:A10").Address(ReferenceStyle:=xlR1C1, External:=True)

Actually Norie this is not for me This is for the below thread I am working for...

http://www.excelforum.com/excel-form...71#post3255871

Please refer the Leave Card Sheet of attached file...

The OP is a 2003 user and the formula which we have suggested seems to be a hectic approach to the OP, so I thought to create an UDF so that the OP can just select the ranges and the error handler and the lengthy formula's will be taken care by the VBA coding...

Please refer the A18 to B23 range of Leave Card Sheet where the suggested formula is applied...

I would like to evaluate the same formula using an UDF and trying to get the A18 cell value in A7 GetResult UDF...

I hope it's clear now... Just wondering how I can use array combination and store it in a Variable

SixthSense

If you want to get the worksheet name use <worksheet>.Name or if you don't have a worksheet reference use <range>.Parent.Name

I have not looked at the workbook but evaluate is limited to 255 characters and I suspect your external reference style is taking you over that limit

@ JosephP,

Thanks for pointing it and me too read that in Microsoft site whether anyother method which gets the reference without filename and including the sheet reference or we have to use Instr() function and split and assign the reference in a variable and refer it in formula?

Little confused.

I was just trying to help with the problem you were having with this formula and converting it to R1C1 as yudlugar suggested.

When I look at that workbook it kind of looks like it could all, or a lot of it anyway, be done in code.

Mind you there's 3 pages of posts that I've not looked through, so I've probably totally missed the plot.

@ Norie,

Forget about that link... Yeah even I won't do that when the thread have lot of posts...

I just want to convert the A18 cell formula of that file to a UDF... But not getting the method about how to use a formula in a variable as array...

Perhaps simply

vResult = "INDEX('" & rRes.Worksheet.Name & "'!" & rRes.Address & ",SMALL(IF(('" _
    & rEmpNo.Worksheet.Name & "'!" & rEmpNo.Address & "='" & vEmpNo.Worksheet.Name & "'!" & vEmpNo.Address & ")*('" & rDate.Worksheet.Name & "'!" & rDate.Address & ">='" _
    & vStDate.Worksheet.Name & "'!" & vStDate.Address & ")*('" & rDate.Worksheet.Name & "'!" & rDate.Address & "<='" & vEndDate.Worksheet.Name & "'!" & vEndDate.Address & "),ROW(" _
    & rDate.Address & ")-ROW(" & rDate.Cells(1).Address & ")+1),ROW(A1)))"

GetResult = Evaluate(vResult)

Originally Posted by JosephP

Perhaps simply

It's working fine Joseph, I am just thinking why microsoft is not included another argument which enables/disables the workbook name in address output.

Thanks a lot for Norie for suggesting some more methods of referencing to the worksheet

One more question, dragging the UDF down is not reacting in the same way like using the row(a1) in evaluate function. The Row(a1) stays as it is and returning 1 as result and due to this the small function results the first lowest match.. any other method can be used instead of Row(a1)

Thanks a lot for both of you for providing your valuable suggestions/solutions

The ROW(A1) is fixed in the code-perhaps pass it as an additional argument

Ah!! Don't know how I forgot that method Thanks for the right suggestion