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

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

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

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

Excel Formula Paste
 

Hi macropod

Thanx - it works super


Kind Regard

--
Message posted from http://www.ExcelForum.com