Works a treat,

Thank you very much.

"Patrick Molloy" wrote:

i created a named range MyData for 'Dispatch Summary'!A1:CA5000

then

Range("D8").FormulaArray =
"=IF(ISERROR(INDEX(mydata,SMALL(IF(mydata=$B$1,ROW (mydata)),ROW(1:1)),2)),""zzz"",INDEX(mydata,SMALL (IF(mydata=$B$1,ROW(mydata)),ROW(1:1)),2))"

worked fine



"Per Jessen" wrote in message
...
Hi

It seems that you need a cell reference:

Range("A1").FormulaArray=.....

Hopes this helps

--
Per

"Simon - M&M" skrev i meddelelsen
...
Hi,

I'm trying to record/write an array formula in VBA with an ISERROR
formula
on the front.
I've tried recording and writing it with no success. When recording I got
an
Unable to Record error and when writing I got a unable to set the
FormulaArray property of the range class error.
The formula i have is
FormulaArray = _
"=INDEX('Dispatch Summary'!R[-4]C:R[4995]C[78],SMALL(IF('Dispatch
Summary'!R[-4]C:R[4995]C[78]=R1C2,ROW('Dispatch
Summary'!R[-4]C:R[4995]C[78])),ROW(R[-4])),2)"


The formula i need is

=IF(ISERROR(INDEX('Dispatch Summary'!A1:CA5000,SMALL(IF('Dispatch
Summary'!A1:CA5000=$B$1,ROW('Dispatch
Summary'!A1:CA5000)),ROW(1:1)),2)),"zzz",INDEX('Di spatch
Summary'!A1:CA5000,SMALL(IF('Dispatch
Summary'!A1:CA5000=$B$1,ROW('Dispatch
Summary'!A1:CA5000)),ROW(1:1)),2))

Is it possible to do?

Thanks for any help.

Simon