Excel Formula Paste

[poppy]

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

 

[macropod]

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

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

 

[macropod]

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

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

 

[poppy]

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

 

[macropod]

Hi poppy,

If I understand the issue correctly, the problem is that your routines don't
specify which rows to modify.

Try something like:
Sub Test()
For i = 3 To 31 Step 2
For j = 3 To 11 Step 1
With Cells((j), (i))
.FormulaR1C1 = "=RC[-1]/SUM(R3C[-1]:R11C[-1])"
.NumberFormat = "0%"
.HorizontalAlignment = xlCenter
End With
Next
Next
End Sub

Cheers

Hi again

I have taken a snippet of code provided on this board by macropod and I
am trying to make it even more simple for my application. Unfortunatley
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 %. I
just want this value to be insert into every second column starting
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 to
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 fine
except that it overwrites my column headings resulting in "#VALUE!".
The Formula also extends over the last row containing data - resulting
in a long list of unnecessary "0%"

I hope I'm making sense

Looking forward to your input.

Thanx

Kind Regards

 

[poppy]

Hi macropod

Thanx - it works super


Kind Regard