Writing Array formulas in code

[Simon - M&M]

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('Dispatch
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

 

[Per Jessen]

Hi

It seems that you need a cell reference:

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

Hopes this helps

 

[Patrick Molloy]

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

Hi

It seems that you need a cell reference:

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

Hopes this helps

--
Per

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('Dispatch
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

 

[Joel]

Results = Evaluate("ISERROR(INDEX('Dispatch
Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch
Summary'!A1:CA5000)),ROW(1:1)),2)")
if Results = true then
Results = "zzz"
else
Results = Evaluate("INDEX('Dispatch
Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch
Summary'!A1:CA5000)),ROW(1:1)),2)")
end if

=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('Dispatch
Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch
Summary'!A1:CA5000)),ROW(1:1)),2))

 

[Simon - M&M]

Works a treat,

Thank you very much.

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

Hi

It seems that you need a cell reference:

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

Hopes this helps

--
Per

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('Dispatch
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