FormulaArray question in VBA

[Tommy Flynn]

FormulaArray does not work with a formula greater than 255 characters. As a
plan "B", I inserted the long formula (greater than 255 characters) in
Cells(r,c) using .Formula and the following code:


theFormula = Cells(r,c).Formula
Cells(r,c).clear
Cells(r,c).FormulaArray = theFormula


This did not work either.........any suggestions?

 

[Bob Phillips]

Tommy,

Presumably, if your formula is greater than 255 characters, some of it is
repeated. Could you try adding a name for that repeated code, for instance

ActiveWorkbook.Names.Add Name:="myFormula", RefersTo:= _
"=IF($A$1=$A$2,""Yes"",""No"")"

and then use myFormula in the 'real' formula

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Charles Williams]

Hi Tommy,

in addition to using names you may be able to shorten the formula by using
very short sheet names.
If neccessary you can rename the worksheets to short names, insert the array
formula, then rename the sheets to the long names.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm