Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Formula Paste
Hello
I am trying to copy a formula from one column to another, but the funn thing is when I copy the formula to another column, the formula is no updated in the new column. For example if I copy this formula from C to E3 =B3/SUM($B$3:$B$11) Code in VBA: Code ------------------- Range("C3").Select ActiveCell.FormulaR1C1 = "=RC[-1]/SUM(R3C2:R11C2)" Selection.AutoFill Destination:=Range("C3:C11"), Type:=xlFillDefault Range("C3").Select Selection.Copy Range("E3,G3,I3,K3,M3,O3").Select ActiveSheet.Past ------------------- the code in E3 appears as: =D3/SUM($B$3:$B$11) instead o =D3/SUM($D$3:$D$11) and G3 as: =F3/SUM($B$3:$B$11) instead o =F3/SUM($F$3:$F$11) and so forth with other columns that I copy to. Do you have any idea why? Plz help if you do Kind Regard -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Formula Paste
Hi poppy,
Excel is copying the formula correctly. Your problem is with the formula itself. Instead of using "=RC[-1]/SUM(R3C2:R11C2)" in C3, which returns =B3/SUM($B$3:$B$11), try: "=RC[-1]/SUM(R3C[-1]:R11C[-1])" which returns =B3/SUM(B$3:B$11) and, when copied to another cell (eg E3), will update the column references correctly. Actually, you code could also be made much more efficient. For example: Sub Test() Range("C3:C11,E3,G3,I3,K3,M3,O3").FormulaR1C1 = "RC[-1]/SUM(R3C[-1]:R11C[-1])" End Sub Cheers "poppy " wrote in message ... Hello I am trying to copy a formula from one column to another, but the funny thing is when I copy the formula to another column, the formula is not updated in the new column. For example if I copy this formula from C3 to E3 =B3/SUM($B$3:$B$11) Code in VBA: Code: -------------------- Range("C3").Select ActiveCell.FormulaR1C1 = "=RC[-1]/SUM(R3C2:R11C2)" Selection.AutoFill Destination:=Range("C3:C11"), Type:=xlFillDefault Range("C3").Select Selection.Copy Range("E3,G3,I3,K3,M3,O3").Select ActiveSheet.Paste -------------------- the code in E3 appears as: =D3/SUM($B$3:$B$11) instead of =D3/SUM($D$3:$D$11) and G3 as: =F3/SUM($B$3:$B$11) instead of =F3/SUM($F$3:$F$11) and so forth with other columns that I copy to. Do you have any idea why? Plz help if you do Kind Regards --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.718 / Virus Database: 474 - Release Date: 9/07/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Formula Paste
Oops,
New code should have been: Sub Test() Range("C3:C11,E3,G3,I3,K3,M3,O3").FormulaR1C1 = "=RC[-1]/SUM(R3C[-1]:R11C[-1])" End Sub (dropped an '=' sign) Cheers "poppy " wrote in message ... Hello I am trying to copy a formula from one column to another, but the funny thing is when I copy the formula to another column, the formula is not updated in the new column. For example if I copy this formula from C3 to E3 =B3/SUM($B$3:$B$11) Code in VBA: Code: -------------------- Range("C3").Select ActiveCell.FormulaR1C1 = "=RC[-1]/SUM(R3C2:R11C2)" Selection.AutoFill Destination:=Range("C3:C11"), Type:=xlFillDefault Range("C3").Select Selection.Copy Range("E3,G3,I3,K3,M3,O3").Select ActiveSheet.Paste -------------------- the code in E3 appears as: =D3/SUM($B$3:$B$11) instead of =D3/SUM($D$3:$D$11) and G3 as: =F3/SUM($B$3:$B$11) instead of =F3/SUM($F$3:$F$11) and so forth with other columns that I copy to. Do you have any idea why? Plz help if you do Kind Regards --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.718 / Virus Database: 474 - Release Date: 9/07/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Formula Paste
Hi again
I have taken a snippet of code provided on this board by macropod and am trying to make it even more simple for my application. Unfortunatle I'm having some problems. Problem 1: Code ------------------- For i = 3 To 31 Step 2 Columns(i).Value = "%" Columns(i).HorizontalAlignment = xlCenter Next ------------------- This piece of code just makes ALL the values of the chosen columns %. just want this value to be insert into every second column startin from the third row of the sheet as headings for that column. Problem 2: Code ------------------- Range("C3:C11,E3,G3,I3,K3,M3,O3").FormulaR1C1 = "=RC[-1]/SUM(R3C[-1]:R11C[-1])" ------------------- This code works fine except that when a new month is added, I have t constantly update the code with the new columns. Code ------------------- For i = 3 To 31 Step 2 Columns(i).FormulaR1C1 = "=RC[-1]/SUM(R3C[-1]:R11C[-1])" Columns(i).NumberFormat = "0%" Next ------------------- I have been trying to use the above code instead. This also works fin except that it overwrites my column headings resulting in "#VALUE!" The Formula also extends over the last row containing data - resultin in a long list of unnecessary "0%" I hope I'm making sense Looking forward to your input. Thanx Kind Regard -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Formula Paste
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using formula or similar to paste maximum value in cell in excel 2 | Excel Discussion (Misc queries) | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
copy and paste an excel formula | New Users to Excel | |||
Why won't Excel let me copy and paste my concantenate formula now | Excel Worksheet Functions | |||
Can an Excel formula be created to paste a picture in a cell? | Excel Worksheet Functions |