FormulaArray for formula more than 255 characters

nileshvk

New Member
Joined
Dec 18, 2014
Messages
11
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
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,716
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 18, 2014
Messages
11
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-questions/687125-long-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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,732
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

New Member
Joined
Dec 18, 2014
Messages
11
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

New Member
Joined
Dec 18, 2014
Messages
11
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,401
Office Version
2010
Platform
Windows
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,732
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.

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))))[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
 

Forum statistics

Threads
1,077,778
Messages
5,336,248
Members
399,072
Latest member
abublitz

Some videos you may like

This Week's Hot Topics

Top