excel vba arrayformula with filtered range too long

Question

I'm trying to apply an ArrayFormula to a filtered range - i.e. add 2 to each cell in filtered set rng and paste result into neighbor cell of rng2 - like this:

 Set rng = Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
 Set rng2 = rng.Offset(0, 1)
 rng2.FormulaArray = "=" & rng.Address & "+ 2"

This gives an error "Unable to set the FormulaArray property of the Range class"

which means the string is too long (greater than 255). rng.Address is

$M$28,$M$480,$M$874:$M$875,$M$889,$M$1111,$M$1302,$M$1410,$M$1413:$M$1415,$M$1994,$M$2319,$M$2322:$M$2323,$M$2327:$M$2329,$M$2385,$M$2884,$M$3049,$M$3130,$M$3192,$M$3242,$M$3287:$M$3288,$M$3406,$M$3418,$M$3423,$M$3610,$M$4001,$M$4008,$M$4104,$M$4145,$M$4374

I've seen some posts using the replace formula but this may be a different case here:

rng will change in each iteration of a for loop as different filter are applied
The rng.address method does not capture all cells: I can see in the filtered excel that after $M$4374, there are more cells in this range. I used MsgBox(rng.address) to see all cells in that range... Is there a way to see all cells in that range, or is there a character cap for rng.address?

Have you tried making that address as a named range? I have gotten around similar issues (particularly in data validation) by selecting all cells i want and labeling it "a". — Cyril
Are you trying to add 2 to the cell to the left? Why the array formula, why not just rng2.FormulaR1C1 = "= RC[-1]+ 2"? — Scott Craner
@ScottCraner: Cool, thanks a lot! I was just thinking how I would do it in excel (with ArrayFormula) and tried to translate it to VBA, but then encountered this cumbersome 255 char restriction. Your method is far easier! — SuperMartingale

SuperMartingale SuperMartingale · Accepted Answer · 2017-03-31T14:26:17

This solves the problem without using ArrayFormula

Set rng = Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
 Set rng2 = rng.Offset(0, 1)
 rng2.FormulaR1C1 = "= RC[-1] + 2"

excel vba arrayformula with filtered range too long

1 Answers