Copy and Paste a formula as Values in a fixed Range as a loop statement using VBA

[Edward S]

I have 3 work sheets i.e. a "Summary" sheet, a "Phase1" and a "Phase2"
sheet The Phase1 and Phase2 sheets are a result of certain parameters
that are entered separately for each of the Phases that produces the
results in them. All the 3 sheets have fixed ranges and they are
A2:AC151, the dates are within the range C3:AC150 for each Phase. All
the 3 sheets have the same structure something like this
A B C D E
SAP NO CostCenter Dec-03 Jan-04 Feb-04
422100 DTF-Office 60,000 186,000 174,000
422140 DTF-Office 40,000 124,000 116,000
422310 DTF-Office 5,243 5,243 -
422320 DTF-Office 1,075 - -
422340 DTF-Office 1,368 - -
442270 DTF-Office 65,000 201,500 188,500
442270 Wells 44,040 136,524 127,716
443900 DTF-Office 10,000 31,000 29,000
443900 DTF-Shorebase 18,000 55,800 52,200
443900 Wells 500 1,550 1,450

The Summary sheet sums up the Phase1 and Phase2, but Phase1 and 2 do
not necessarily have the same period for instance, Phase1 could start
from Dec-03 to Apr-05 and Phase2 could start from Jan-04 to Nov-05.
The Summary sheet would then start
Dec-03 to Nov-05, hence in order to arrive at the correct total for
each of the line items I have to enter a "Sumif" formula in the
Summary sheet. As the "sumif" takes a long time to calculate, I am
working on a different alternative and that is have VBA do the job for
me in a much faster and efficient way. Say I have stored and hidden
the formula as below in Cell A1, I want VBA to enter the same Formula
from C3 as described below and then copy and paste the formula in Cell
C3 as Value, then go to the next cell that is D3 enter the same
formula in this Cell and then Copy and Paste it as value, and carry on
till it reaches AC150.

I want someone to construct the code for me such that as I click on
the Summary Sheet the code activates automatically. I believe this
would be a loop statement

Formula:
{=SUM(IF(Phase1!$A$3:$A$150=Summary!$A1,IF(Phase1!$B$3:$B$150=Summary!$B1,IF(Phase1!$C$2:$AC$2=Summary!C$1,Phase1!$C$3:$AC$150,0),0),0))+SUM(IF(Phase2!$A$3:$A$150=Summary!$A1,IF(Phase2!$B$3:$B$150=Summary!$B1,IF(Phase2!$C$2:$AC$2=Summary!C$1,Phase2!$C$3:$AC$150,0),0),0))}

 

[mudraker]

Edward

Try this on a backup copy of your data


Sub dddd()

With Sheets("summary")

.Range("c2").FormulaArray = _
"=SUM(IF(phase1!R3C1:R150C1=summary!RC1," _
& "IF(phase1!R3C2:R150C2=summary!RC2," _
& "IF(phase1!R2C3:R2C29=summary!R1C," _
& "phase1!R3C3:R150C29,0),0),0))"

.Range("c2").AutoFill Destination:=Range("c2:c150"), _
Type:=xlFillDefault

.Range("c2:c150").Copy

.Range("c2:c150").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End Su