Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent Help needed on! Copy and Paste a Formula as Values using a Loop statement
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent Help needed on! Copy and Paste a Formula as Values using a Loop statement
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. -- Regards, Tom Ogilvy "Edward S" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent Help needed on! Copy and Paste a Formula as Values using a Loop statement
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))} |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent Help needed on! Copy and Paste a Formula as Values using a Loop statement
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent Help needed on! Copy and Paste a Formula as Values using a Loop statement
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
Loop Statement through If Not IsEmpty Then Paste into Destination | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
copy,paste and loop through workbook | Excel Discussion (Misc queries) | |||
copy and paste loop | Excel Programming |