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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617
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

ADVERTISEMENT

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
10,617
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

ADVERTISEMENT

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
10,617
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,906
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top