Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: FormulaArray for formula more than 255 characters

  1. #1
    New Member
    Join Date
    Dec 2014
    Posts
    11

    Default FormulaArray for formula more than 255 characters

    Hi Guys,

    I am trying to FormulaArray in my code and it does not work for formula having more than 255 characters. I have already tried couple of solutionas given on link below and this does not work for me

    http://www.excelforum.com/excel-prog...ml#post3932754
    Solution on this link does not work for formula with more than 255 characters

    http://dailydoseofexcel.com/archives...comment-694109
    Solution on this link gives me error: 'application-defined or object-defined '

    here is my formula:

    Worksheets("WorkSheet1").Range("B1:B12").FormulaArray = "=IF(ISERROR(MEDIAN(IF(WorkSheet!R2C4:R10000C4=R50C2,IF(WorkSheet!R2C7:R10000C7=R51C2,IF(WorkSheet!R2C12:R10000C12=RC1,IF (WorkSheet!R2C18:R10000C18=R2C17,WorkSheet!R2C13:R10000C13)))))),0,MEDIAN(IF(WorkSheet!R2C4:R10000C4=R50C2,IF(WorkSheet! R2C7:R10000C7=R51C2,IF(WorkSheet!R2C12:R10000C12=RC1,IF(WorkSheet!R2C18:R10000C18=R2C17,WorkSheet!R2C13:R10000C13))))))"

    Any help in this is highly appreciated.....

    Thanks,
    -N

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,797

    Default Re: FormulaArray for formula more than 255 characters

    Hi
    Welcome to the board

    An array formula with more than 255 characters cannot be entered directly. You have to split it, keeping always a correct formula syntax, and use Range.Replace() to build the formula.

    I posted a solution with examples here:

    http://www.mrexcel.com/forum/excel-q...lications.html

    Hope it helps.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Posts
    4,293

    Default Re: FormulaArray for formula more than 255 characters

    Are you on 2007 or later? If so you can use iferror construction of the formula and halve its size.

    =IFERROR(MEDIAN(IF(WorkSheet!RC24:RC100004=RC502,IF(WorkSheet!RC27:RC100007=RC512,IF(WorkSheet!RC212:RC1000012=RC1,IF(Wo rkSheet!RC218:RC1000018=RC217,WorkSheet!RC213:RC1000013))))),0)

  4. #4
    New Member
    Join Date
    Dec 2014
    Posts
    11

    Default Re: FormulaArray for formula more than 255 characters

    Quote Originally Posted by pgc01 View Post
    Hi
    Welcome to the board

    An array formula with more than 255 characters cannot be entered directly. You have to split it, keeping always a correct formula syntax, and use Range.Replace() to build the formula.

    I posted a solution with examples here:

    http://www.mrexcel.com/forum/excel-q...lications.html

    Hope it helps.
    I tried to generate code as per suggestion given on above link:

    Dim str1 As String, str2 As String, str3 As String

    str1 = "=IF(ISERROR(MEDIAN(IF(WorkSheet1!R2C4:R10000C4=R50C2,IF(WorkSheet1!R2C7:R10000C7=R51C2,IF(WorkSheet1!R2C12:R10000C12=RC1 ,AAA,BBB"
    str2 = "IF(WorkSheet1!R2C18:R10000C18=R2C17,WorkSheet1!R2C13:R10000C13)))))),0,MEDIAN(IF(WorkSheet1!R2C4:R10000C4=R50C2"
    str3 = "IF(WorkSheet1!R2C7:R10000C7=R51C2,IF(WorkSheet1!R2C12:R10000C12=RC1,IF(WorkSheet1!R2C18:R10000C18=R2C17,WorkSheet1!R2C13 :R10000C13))))))"


    With Worksheets("WorkSheet").Range("B52:B63")
    .FormulaArray = str1 - line 1
    .Replace "AAA", str2 - lin2 2
    .Replace "BBB", str3 - line 3
    End With

    it gives me error on line 1 : unable to set the FormulaArray property of the Range Class

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,797

    Default Re: FormulaArray for formula more than 255 characters

    Hi

    As I explain in post #2 in the link, each partial formula must respect the formula syntax.

    Your Str1 does not respect the formula syntax.

    Try entering it in a cell and you'll see that it won't accept it.
    Str1 must be a formula with a correct syntax. If you enter it in a cell it will be accepted as a valid formula.

    Please read post #2 in the link I posted, I have there 2 examples, the first one incorrect, like yours, and the second one with the correct syntax, that works OK.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    New Member
    Join Date
    Dec 2014
    Posts
    11

    Default Re: FormulaArray for formula more than 255 characters

    Quote Originally Posted by pgc01 View Post
    Hi

    As I explain in post #2 in the link, each partial formula must respect the formula syntax.

    Your Str1 does not respect the formula syntax.

    Try entering it in a cell and you'll see that it won't accept it.
    Str1 must be a formula with a correct syntax. If you enter it in a cell it will be accepted as a valid formula.

    Please read post #2 in the link I posted, I have there 2 examples, the first one incorrect, like yours, and the second one with the correct syntax, that works OK.

    Alright, changed my code to satisfy this condition:

    With Worksheets("Worksheet").Range("B102:B113")
    .FormulaArray = "=IF(ISERROR(AAAA)),0,AAAA))"
    .Replace "AAA", "MEDIAN(IF(Worksheet1!$D:$D=$B$100,IF(Worksheet1!$G:$G=$B$101,IF(Worksheet1!$L:$L=$A102,IF(Worksheet1!$R:$R=$Q$9,Workshee t1!$M:$M))))", LookAt:=xlPart
    End With

    It gives me error: "unable to set FomulaArray poperty to class Range"

    Any Clue on this?

    Also Isnt there any other approach to resolve this issue?

  7. #7
    New Member
    Join Date
    Dec 2014
    Posts
    11

    Default Re: FormulaArray for formula more than 255 characters

    Quote Originally Posted by steve the fish View Post
    Are you on 2007 or later? If so you can use iferror construction of the formula and halve its size.

    =IFERROR(MEDIAN(IF(WorkSheet!RC24:RC100004=RC502,IF(WorkSheet!RC27:RC100007=RC512,IF(WorkSheet!RC212:RC1000012=RC1,IF(Wo rkSheet!RC218:RC1000018=RC217,WorkSheet!RC213:RC1000013))))),0)
    I am using excel 2010. I tried approach given above. I get error "Object doesn't support property or Method"

    Any clue?

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    28,852

    Default Re: FormulaArray for formula more than 255 characters

    Quote Originally Posted by nileshvk View Post
    Alright, changed my code to satisfy this condition:

    With Worksheets("Worksheet").Range("B102:B113")
    .FormulaArray = "=IF(ISERROR(AAAA)),0,AAAA))"
    .Replace "AAA", "MEDIAN(IF(Worksheet1!$D:$D=$B$100,IF(Worksheet1!$G:$G=$B$101,IF(Worksheet1!$L:$L=$A102,IF(Worksheet1!$R:$R=$Q$9,Workshee t1!$M:$M))))", LookAt:=xlPart
    End With

    It gives me error: "unable to set FomulaArray poperty to class Range"
    The green highlighted text contains four A's whereas the red highlighted text contains only three A's... try making the red text contain four A's and see what happens.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  9. #9
    New Member
    Join Date
    Dec 2014
    Posts
    11

    Default Re: FormulaArray for formula more than 255 characters

    Quote Originally Posted by Rick Rothstein View Post
    The green highlighted text contains four A's whereas the red highlighted text contains only three A's... try making the red text contain four A's and see what happens.
    No luck

  10. #10
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,797

    Default Re: FormulaArray for formula more than 255 characters

    Quote Originally Posted by nileshvk View Post
    Alright, changed my code to satisfy this condition:

    With Worksheets("Worksheet").Range("B102:B113")
    .FormulaArray = "=IF(ISERROR(AAAA)),0,AAAA))"
    .Replace "AAA", "MEDIAN(IF(Worksheet1!$D:$D=$B$100,IF(Worksheet1!$G:$G=$B$101,IF(Worksheet1!$L:$L=$A102,IF(Worksheet1!$R:$R=$Q$9,Workshee t1!$M:$M))))", LookAt:=xlPart
    End With

    It gives me error: "unable to set FomulaArray poperty to class Range"

    Any Clue on this?
    Hi

    The logic in your code is correct, but you were missing a closing parenthesis at the end.

    Code:
    With Worksheets("Worksheet").Range("B102:B113")
        .FormulaArray = "=IF(ISERROR(9999),0,9999)"
        .Replace "9999", "MEDIAN(IF(Worksheet1!$D:$D=$B$100,IF(Worksheet1!$G:$G=$B$101,IF(Worksheet1!$L:$L=$A102,IF(Worksheet1!$R:$R=$Q$9,Worksheet1!$M:$M)))))", LookAt:=xlPart
    End With
    Quote Originally Posted by nileshvk View Post
    Also Isnt there any other approach to resolve this issue?
    If you have excel 2007+, Steve already gave you another option in post #3
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com