Urgent Help needed on! Copy and Paste a Formula as Values using a Loop statement

[Edward S]

I have posted this earlier but did not get any reponse, hopefully I
have simplyfied it here, here is what I want VBA to do. I have 2
sheets a "Summary" and a "Phase1" work sheet:

Range on the "Summary" work sheet is E3:BY139

I want to copy the Formula as an array below, starting in Cell E3 then
copy and paste it as a value before moving to next cell which could be
either E4 or F3. This loop should go on until the last cell i.e.
E139.

Now CEP1, CCP1, DTP1 and RGP1 are all named ranges where Phase1 is
another sheet

CCP1 = Phase1!$C$3:$C$150
CEP1 = Phase1!$B$3:$B$150
DTP1 = Phase1!$E$2:$BV$2
RGP1 = Phase1!$E$3:$BV$150

Formula entered as an array in Cell E150:
{=SUM(IF(CEP1=$B3,IF(CCP1=$C3,IF(DTP1=E$11,RGP1,0),0),0))}

Can someone please help me on this one, Many Many thanks in advance

Edward

 

[Tom Ogilvy]

With Worksheets("Summary").Range("E3:BY139")
.Value = .Value
End with

will convert formulas to values.

Not sure what you want, but that is my best guess.

 

[keepITcool]

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]

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 >

{=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

 

[Tom Ogilvy]

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

keepITcool <[email protected]> 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 >

{=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