I didn't notice the length of the string.

If you want more info, take a look at Dick Kusleika's site:
http://www.dailydoseofexcel.com/arch...rmulas-in-vba/

It's a technique to workaround that .formulaarray length limit.

diepvic wrote:

Thanks you guys for your support.
I've been advised a macro as below:

Dim myFormula1 as string
Dim myFormula2 as string

myFormula1 = " +U24-SUM(IF(('[Data
process.xls]Sundry-LEC'!$J$1:$J$9=$A$24)*('[Data
process.xls]Sundry-LEC'!$F$1:$F$9=$B$6),'[Data
process.xls]Sundry-LEC'!$G$1:$G$9,0))" & "+1"

myFormula2 = "-SUM(IF((('[Data process.xls]BS (excl.
Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028050"")+('[Data process.xls]BS
(excl. Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028055""))*('[Data
process.xls]BS (excl. Off PL)'!$M$1:$M$" & lngLastRowBS & "=$B$6),'[Data
process.xls]BS (excl. Off PL)'!$N$1:$N$" & lngLastRowBS & ",0))"

With ActiveSheet.Range("A1")
.FormulaArray = myFormula1
.Replace "+1",myFormula2
End with

--

Dave Peterson