Getting around .FormulaArray limits
 

You could use a name range in the worksheet. for example Sheet1!A1:A100
could be reduced to Sh1 saving a lot of characters.

"Tim Mayes" wrote:

I've run into a problem trying to insert an array formula that is longer than
255 characters. I'm using Excel 2007 at the moment, but I expect that it has the
same limitations in earlier versions. Basically, I'm doing this:

OutputRng.FormulaArray = "a string with more than 255 characters"

Obviously, it is an actual formula, and it works fine when manually entered. So,
given that my test formula has 263 characters, I assume that this is why I'm
getting an error. If I leave sheet names out of the formula, then it is short
enough to work just fine, but then I won't be able to place my output on a
different sheet from the data that it references.

Does anybody have a workaround for entering long array formulas? The only thing
that I can think of is to insert the formula without the sheet names and then do
a Find and Replace in the worksheet. Yuck.

Thanks,

Tim