View Full Version : [SOLVED:] Copying Formulas
Erays
04-09-2005, 05:59 AM
I know that there has to be a way, but I don't know how, I would like to copy multiple formulas and insert them in a different place on the worksheet or on another sheet. when I copy the cells and paste special the formulas it paste the formulas but changes the value of the formula.
Glaswegian
04-09-2005, 08:08 AM
Hi Erays
Sounds like you may need to make your formula references absolute. Say you have this fomula in cell C1
=SUM(A1:B1)
Click on A1 in the formula bar - now press F4. The "A" will now be surrounded with dollar signs and should look like this
=SUM($A$1:B1)
You've now made the formula reference to cell A1 absolute. When you copy and paste it will remain as A1. Do the same for B1.
If you just wanted the column reference to remain absolute then it would be
=SUM($A1:B1)
and for rows it would be
=SUM(A$1:B1).
Does this help?
Regards
Erays
04-09-2005, 08:19 AM
If the formulas are already on the page will I have to change each one and then copy them
Glaswegian
04-09-2005, 09:20 AM
Hi
I thought this code might work
Sub ChangeCellRefs()
Dim myRng As Range
Dim c As Range
Set myRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
For Each c In myRng
Application.ConvertFormula Formula:=c.Formula, fromReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute
Next
End Sub
but it's not - and I can't figure out why :dunno .
See if it works for you - I'll keep trying.
Regards
TonyJollans
04-09-2005, 01:57 PM
I'm not over-familiar with this but it appears to work this way ...
c.Formula = Application.ConvertFormula(Formula:=c.Formula, fromReferenceStyle:=xlA1, toReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Glaswegian
04-10-2005, 08:43 AM
Nice one Tony - I was not really familiar with that myself so it was a bit of a learning curve!
Regards
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.