VBA FormulaArray

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
I am having some trouble with inserting an Array formula

LR_Formula = "=IFERROR(IF(ROWS($A$5:A5)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),1)),"",INDEX(INDIRECT($B$1),SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROWS($A$5:A5)))),"")"

With Worksheets("Sheet1").Range("A5")
.FormulaArray = LR_Formula
.Value = .Value
End With


this is just one of a few scenarios I have tried and am open to others

the Error is invalid property range. I am aware it is > 255 characters, however I have tried other array formulas under 255 and still not working
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
Without parsing out that entire formula, and without worrying about the 255 length yet, I see at least one problem:

LR_Formula = "=IFERROR(IF(ROWS($A$5:A5)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),1)),"",INDEX(INDIRECT($B$1),SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROWS($A$5:A5)))),"")"

To define a string, you put it between quote marks (") which I've marked in red. If you want to include a quote mark within your string, you need to double it ("") so that Excel recognizes that it's part of the string, and not a delimiter. If you have 2 quote marks together, which I've marked in blue, you need to double each one, meaning you need to use """" instead for all the marked sections.

Check Rick's comments in posts 3 and 4 of this thread for a more detailed explanation:
http://www.mrexcel.com/forum/excel-questions/984187-how-paste-script-given-cell.html

As far as the length, that's a thornier question.
 
Last edited:

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
Thank you for the responses, I am using the common work around for arrays > 255 characters, but I am still running into an issue with the property class. I am hoping its just a syntax error


Range("A10").FormulaArray = "=IFERROR(IF(ROWS($A$10:A10)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),1)),"""",INDEX(INDIRECT($B$1),""X_X_X""),"""")"
Range("A10").Replace """X_X_X""", "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$10:A10))))"
 

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
I just noticed I had one extra parenthesis at the end of the replacement, but it still didn't fix the issue
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
I still see 1 instance of the "" issue:

Range("A10").Replace """X_X_X""", "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$10:A10))))"
 

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
still no dice after fix; same error

Range("A5").FormulaArray = "=IFERROR(IF(ROWS($A$10:A10)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),1)),"""",INDEX(INDIRECT($B$1),""X_X_X""),"""")"
Range("A5").Replace """X_X_X""", "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$10:A10))))"
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
Try:

Range("A5").FormulaArray = "=IFERROR(IF(ROWS($A$10:A10)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),1)),"""",INDEX(INDIRECT($B$1),""X_X_X"")),"""")"

Range("A5").Replace """X_X_X""", "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$10:A10))))"

Even that may not work, since I haven't parsed your entire formula to see what it does. If you still have problems, select the entire formula from the first line, including the surrounding quote marks, copy it, then go to the immediate window and type PRINT and paste the formula. It should print out the formula with all the quotes resolved. Then copy that version and paste it into a cell in Excel, and make sure that you don't get any errors there. If so, fix them, then reverse the process with the "" marks.
 

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
Eric W,

thank you for the assist, I was able to figure out an solution/alternative

Area_1 = "=IFERROR(IF(ROWS($A$5:A5)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),1)),"""",INDEX(INDIRECT($B$1),X_X_X)),"""")"
Area_2 = "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$5:A5))"
With Sheet17.Range("A5")
.FormulaArray = Area_1
.Replace "X_X_X", Area_2
End With
 

Forum statistics

Threads
1,077,780
Messages
5,336,256
Members
399,072
Latest member
abublitz

Some videos you may like

This Week's Hot Topics

Top