+ Reply to Thread
Results 1 to 6 of 6

Please Help!! Macro error

  1. #1
    Brett
    Guest

    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,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!

  2. #2
    Bob Phillips
    Guest

    Re: 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,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)

    "Brett" <[email protected]> wrote in message
    news:[email protected]...
    > 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: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!




  3. #3
    Niek Otten
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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!




  4. #4
    Niek Otten
    Guest

    Re: Please Help!! Macro error

    Try using named ranges instead.

    --
    Kind regards,

    Niek Otten

    "Brett" <[email protected]> wrote in message
    news:[email protected]...
    > 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!




  5. #5
    Dave Peterson
    Guest

    Re: Please Help!! Macro error

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

    http://www.*****-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,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!


    --

    Dave Peterson

  6. #6
    Dave Peterson
    Guest

    Re: 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,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!


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1