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