keepITcool wrote in message . ..
Hope i've interpreted it correctly:
Sub FillFmlArrays()
Dim rng As Range
Worksheets(1).Activate
For Each rng In [e5:e139].Cells
With rng.Resize(1, 77 - 5 + 1)
.FormulaArray = _
"=SUM(IF(CEP1=rc2,IF(CCP1=rc3,IF(DTP1=r11c,RGP1,0) ,0),0))"
.Formula = .Value
End With
Next
End Sub
keepITcool
< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool
(Edward S) wrote:
{=SUM(IF(CEP1=$B3,IF(CCP1=$C3,IF(DTP1=E$11,RGP1,0) ,0),0))}
Thanks Guys, but it was not exactly what I was looking for!
I would like to copy the Formula in Cell E2 which is on the "Summary"
sheet
to Row E4:BY4, then copy and paste the contents in this Row (i.e.
E4:BY4) to Values. Then do the same thing in the next Row that is
E5:BY5....until.....E140:BY140. All of this is in the Range E3:BY140,
I have Named the Range ("MnthBudget"). The Formula is only entered in
Cell E2 as an array Formula. The entire Range ("MnthBudget") copies
the Formula from Cell E2 only, but does it Row by Row (This is done so
that the calculation time is much faster, Below is only a small part
of the Actual Formula)
for example:
If E2 = {=SUM(IF(CEP1=$B2,IF(CCP1=$C2,IF(DTP1=E$1,RGP1,0), 0),0))},
Then after the Formulas are copied in the Row E3:BY3
E4 {=SUM(IF(CEP1=$B4,IF(CCP1=$C4,IF(DTP1=E$1,RGP1,0), 0),0))}
F4 {=SUM(IF(CEP1=$B4,IF(CCP1=$C4,IF(DTP1=F$1,RGP1,0), 0),0))}
..
..
BY4 {=SUM(IF(CEP1=$B4,IF(CCP1=$C4,IF(DTP1=BY$1,RGP1,0) ,0),0))}
When Converted to Values the Formulas in the Row would look like
this...,
E4 = $127,500, F4 = $63,200.....BY4 = $71,100(Placeholder numbers
used)
This would complete the 1st Row in the Range "MnthBudget". Then the
code would do the same thing in the 2nd Row and so on and so forth.
I hope I have tried to elucidate it better
Anticipating your kind response
Many thanks again
Edward