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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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:
Upvote 0
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))))"
 
Upvote 0
I just noticed I had one extra parenthesis at the end of the replacement, but it still didn't fix the issue
 
Upvote 0
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))))"
 
Upvote 0
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))))"
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top