PDA

View Full Version : Populating a range with formulas



ericafor
11-01-2011, 09:01 AM
Hello guys,

Ive got a noob question. I am trying to populate a range with an array formula:
"=SUM((Risk!$K$2:$K$2419)*(Risk!$D$2:$D$2419=E$8)"

I am trying the following code:

Worksheets("Aggregation").Range("e8:e195").Select
Selection.FormulaArray = "=SUM((Risk!$K$2:$K$2419)*(Risk!$D$2:$D$2419=RC[-2])"

Tried putting "" around the RC section but that does not seem to do any good. The above formula gives error 1004: Unable to set formulaarray property of range class.

Please help!

Kenneth Hobs
11-01-2011, 09:48 AM
Welcome to the forum! Click the VBA button to add code tags to paste code between.


The trouble with formulaarrays is that the selected range must be of the same size. See if this gives you an idea.
'http://www.ozgrid.com/forum/showthread.php?t=66047

Sub t1M2Arrays()
M2Arrays Range("B2:G7"), Range("K2:P7"), False
End Sub
Sub t2M2Arrays()
M2Arrays Range("B2:G7"), Range("K2:P7"), True
End Sub
Sub M2Arrays(Array1 As Range, Array2 As Range, boolFormula As Boolean)
Dim str As String
str = "=" & Array1.Address & " * " & Array2.Address

If boolFormula = False Then
ActiveCell.Resize(Array1.Rows.Count, Array1.Columns.Count).FormulaArray = Evaluate(str)
Else:
ActiveCell.Resize(Array1.Rows.Count, Array1.Columns.Count) = Evaluate("{" & str & "}")
ActiveCell.Resize(Array1.Rows.Count, Array1.Columns.Count).FormulaArray = str
End If
End Sub

ericafor
11-01-2011, 11:24 AM
Dont really understand what you meant..sory

Basically the only problem seems to be with the reference to the Criteria cell
Worksheets("Aggregation").Range("e8:e8").FormulaArray = "=SUM((Risk!$K$2:$K$2419)*(Risk!$D$2:$D$2419=RC[-2])"
This is what Ive got, but this works:


Worksheets("Aggregation").Range("e8:e8").FormulaArray = "=SUM((Risk!$K$2:$K$2419))"


What I am basically trying to do is get a sumif with multiple criteria (taken out to reduce confusion), thats why I am using the SUM with * instead of normal sumif. The part thats not working is the reference to the criterion cell. So there doesnt seem to be a problem with the size of the range.

Kenneth Hobs
11-01-2011, 11:38 AM
I don't see why you have the RC routine in it. Do it manually and post the formula or better yet, do it manually and post the workbook.