Please Help!! Macro error
 

I'm trying to run a macro with the following formula in it:

Range("M2").Select
Selection.FormulaArray = _

"=IF(ISERROR(INDEX(R!A2:R5000,MATCH(LARGE(IF(R!A2: T5000=A2,R!Q2:Q5000),1),R!Q2:Q5000,0),14))=FALSE,I NDEX(R!A2:T5000,MATCH(LARGE(IF(R!A2:T5000=A2,R!Q2: Q5000),1),R!Q2:Q5000,0),14),0)"


Everytime I run it, I get the "Unable to set the formulaArray property of
the range class " error". I thought I might have exceeded the 255 limit, but
the formula is only 181 characters!

Please Help!! Macro error
 

If you read help, you will see that FormulaArray needs R1C1 notation, not A1


Range("M2").FormulaArray = _
"=IF(ISERROR(INDEX(R!R2C1:R5000C18,MATCH(LARGE(IF( R!R2C1:R5000C20=R2C1,R!R2C
17:R5000C17),1),R!R2C17:R5000C17,0),14))=FALSE,IND EX(R!R2C1:R5000C20,MATCH(L
ARGE(IF(R!R2C1:R5000C20=R2C1,R!R2C17:R5000C17),1), R!R2C17:R5000C17,0),14),0)
"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brett" wrote in message
...
I'm trying to run a macro with the following formula in it:

Range("M2").Select
Selection.FormulaArray = _


"=IF(ISERROR(INDEX(R!A2:R5000,MATCH(LARGE(IF(R!A2: T5000=A2,R!Q2:Q5000),1),R!
Q2:Q5000,0),14))=FALSE,INDEX(R!A2:T5000,MATCH(LARG E(IF(R!A2:T5000=A2,R!Q2:Q5
000),1),R!Q2:Q5000,0),14),0)"


Everytime I run it, I get the "Unable to set the formulaArray property of
the range class " error". I thought I might have exceeded the 255 limit,
but
the formula is only 181 characters!

Please Help!! Macro error
 

Hi Brett,

The problem is, your formula is 316 (or so) characters in R1C1 Reference
Style, which is what Excel uses internally and which counts for the maximum
of 256.

--
Kind regards,

Niek Otten

"Brett" wrote in message
...
I'm trying to run a macro with the following formula in it:

Range("M2").Select
Selection.FormulaArray = _

"=IF(ISERROR(INDEX(R!A2:R5000,MATCH(LARGE(IF(R!A2: T5000=A2,R!Q2:Q5000),1),R!Q2:Q5000,0),14))=FALSE,I NDEX(R!A2:T5000,MATCH(LARGE(IF(R!A2:T5000=A2,R!Q2: Q5000),1),R!Q2:Q5000,0),14),0)"


Everytime I run it, I get the "Unable to set the formulaArray property of
the range class " error". I thought I might have exceeded the 255 limit,
but
the formula is only 181 characters!

Please Help!! Macro error
 

Try using named ranges instead.

--
Kind regards,

Niek Otten

"Brett" wrote in message
...
I'm trying to run a macro with the following formula in it:

Range("M2").Select
Selection.FormulaArray = _

"=IF(ISERROR(INDEX(R!A2:R5000,MATCH(LARGE(IF(R!A2: T5000=A2,R!Q2:Q5000),1),R!Q2:Q5000,0),14))=FALSE,I NDEX(R!A2:T5000,MATCH(LARGE(IF(R!A2:T5000=A2,R!Q2: Q5000),1),R!Q2:Q5000,0),14),0)"


Everytime I run it, I get the "Unable to set the formulaArray property of
the range class " error". I thought I might have exceeded the 255 limit,
but
the formula is only 181 characters!

Please Help!! Macro error
 

The length of the formula is measured when in R1C1 reference style.

http://www.dicks-blog.com/archives/2...rmulas-in-vba/
(one line in your browser)

May have a workaround.

Brett wrote:

I'm trying to run a macro with the following formula in it:

Range("M2").Select
Selection.FormulaArray = _

"=IF(ISERROR(INDEX(R!A2:R5000,MATCH(LARGE(IF(R!A2: T5000=A2,R!Q2:Q5000),1),R!Q2:Q5000,0),14))=FALSE,I NDEX(R!A2:T5000,MATCH(LARGE(IF(R!A2:T5000=A2,R!Q2: Q5000),1),R!Q2:Q5000,0),14),0)"


Everytime I run it, I get the "Unable to set the formulaArray property of
the range class " error". I thought I might have exceeded the 255 limit, but
the formula is only 181 characters!

--

Dave Peterson

Please Help!! Macro error
 

But that's the same reply you have in the other thread.

Brett wrote:

I'm trying to run a macro with the following formula in it:

Range("M2").Select
Selection.FormulaArray = _

"=IF(ISERROR(INDEX(R!A2:R5000,MATCH(LARGE(IF(R!A2: T5000=A2,R!Q2:Q5000),1),R!Q2:Q5000,0),14))=FALSE,I NDEX(R!A2:T5000,MATCH(LARGE(IF(R!A2:T5000=A2,R!Q2: Q5000),1),R!Q2:Q5000,0),14),0)"


Everytime I run it, I get the "Unable to set the formulaArray property of
the range class " error". I thought I might have exceeded the 255 limit, but
the formula is only 181 characters!

--

Dave Peterson