In the example pointed out by Dave, much of the formula are ranges. The
equation can be reduced to something like this.

As long as you know the limitation of a programming environment there
are almost always ways around it.

Option Explicit

Sub Test()

Dim X_TABLE_FORMULA As String
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range

Set r1 = Worksheets("HISTORIC_DATA").Range("E5:E1130")
r1.Name = "Range1"
Set r2 = Worksheets("HISTORIC_DATA").Range("C5:C1130")
r2.Name = "Range2"
Set r3 = Worksheets("HISTORIC_DATA").Range("H5:K1130")
r3.Name = "Range3"
Set r4 = Worksheets("HISTORIC_DATA").Range("B5:B1130")
r4.Name = "Range4"

X_TABLE_FORMULA =
"=SUM(IF(B$1=Range1,IF($A2=Range2,Range3,0)))/SUM(IF(B$1=Range1,IF($A2=R
ange2,Range4,0)))"
Range("A1").FormulaArray = X_TABLE_FORMULA

End Sub




*** Sent via Developersdex http://www.developersdex.com ***