FormulaArray for formula more than 255 characters

[nileshvk]

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

 

[pgc01]

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-...array-formulas-visual-basic-applications.html

Hope it helps.

 

[steve the fish]

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(WorkSheet!RC218:RC1000018=RC217,WorkSheet!RC213:RC1000013))))),0)

 

[nileshvk]

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-...array-formulas-visual-basic-applications.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

 

[pgc01]

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.

 

[nileshvk]

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,Worksheet1!$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?

 

[nileshvk]

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(WorkSheet!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?

 

[Rick Rothstein]

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,Worksheet1!$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.

 

[nileshvk]

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

 

[pgc01]

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,Worksheet1!$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.

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))))[B][COLOR=#ff0000])[/COLOR][/B]", LookAt:=xlPart
End With

Also Isnt there any other approach to resolve this issue?

If you have excel 2007+, Steve already gave you another option in post #3