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

Hi macropod

Thanx - it works super


Kind Regard

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