Take a look at Dick Kusleika's site:
http://www.dailydoseofexcel.com/arch...rmulas-in-vba/




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

--

Dave Peterson