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 ***