Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Writing Array formulas in code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default Writing Array formulas in code

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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default Writing Array formulas in code

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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default Writing Array formulas in code

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Writing Array formulas in code

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



"Simon - M&M" wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default Writing Array formulas in code

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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Code Optimization for Array Formulas [email protected] Excel Programming 0 October 11th 07 06:46 PM
Array Formulas + VB Code [email protected] Excel Programming 2 March 20th 07 08:08 PM
Writing a range to an array... Alex Excel Programming 2 December 29th 05 01:59 PM
Writing Range to Array Marston Excel Programming 3 August 9th 04 09:11 PM
Writing Array Formulas in VBA Ed Excel Programming 4 January 10th 04 07:27 PM


All times are GMT +1. The time now is 01:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement