I assume when you said
Then after the Formulas are copied in the Row E3:BY3

you meant to say
Then after the Formulas are copied in the Row E4:BY4


Dim rng as Range, cell as Range
set rng = Range("MnthBudget").columns(1).Cells
set rng = rng.offset(1,0).Resize(rows.count-1,1)
for each cell in rng
worksheets("Summary").Cell(2,5).copy _
Destination:=cell.resize(1,73)
cell.Resize(1,73).Formula = cell.Resize(1,73).Value
Next

Still, except for some minor differences, I am not sure how this is
different from what has been given you.

--
Regards,
Tom Ogilvy



"Edward S" wrote in message
om...
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