PDA

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