Copy and Pasting Formulas
 

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!

Copy and Pasting Formulas
 

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

On Thu, 10 Sep 2009 15:17:01 -0700, Lindsey
wrote:

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!

Copy and Pasting Formulas
 

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!

"Gord Dibben" wrote:

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

On Thu, 10 Sep 2009 15:17:01 -0700, Lindsey
wrote:

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!

Copy and Pasting Formulas
 

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.)

Copy and Pasting Formulas
 

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 InsertModule. 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 ToolMacroMacros.

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

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


Gord

On Fri, 11 Sep 2009 06:39:01 -0700, Lindsey
wrote:

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!

"Gord Dibben" wrote:

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

On Thu, 10 Sep 2009 15:17:01 -0700, Lindsey
wrote:

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!