Copy and Pasting Formulas

[Lindsey]

Hi,
I need to copy a formula, but when I paste it, it needs to stay the same
formula.

Example:
I am copying a cell with this formula in A1 that is: =B1+C1 to cell A2.
However, when I paste it in A2, the formula changes to: = B2+C2 and I want it
to stay as =B1+C1.

I understand that I could use $, but this workbook has already been created
by someone else and I am trying to copy and paste a large block of cells,
where I don't want to go through and edit every cell.

Is there a way to do this?

Thanks!

 

[Gord Dibben]

Use one of these macros to change all cells at one whack.

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub

Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelative)
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Lindsey]

Sorry, I do not know how to create macros. If they are easy to create or
there is a website that can introduce me to them, please let me know, thanks!

 

[MyVeryOwnSelf]

I need to copy a formula, but when I paste it, it needs to stay the

same formula.

Example:
I am copying a cell with this formula in A1 that is: =B1+C1 to cell
A2. However, when I paste it in A2, the formula changes to: = B2+C2
and I want it to stay as =B1+C1.

One way is to select A1, then select the text in the formula bar to copy
(instead of copying the cell A1 itself).

Then select A2, click in the formula bar, and paste into the formula bar.

(I have Excel 2003.)

 

[Gord Dibben]

If you're still looking.................

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

I would sugeest you use the Sub Absolute() macro.


Gord