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!
|