Please Help!! Macro error

B

Brett

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(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!
 
B

Bob Phillips

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,INDEX(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)
 
N

Niek Otten

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.
 
Top