Copy without reference cells

L

lrbtav

Hi, I have a few hundred of formulas such as this throughout my excel
file: =IF("L2">1,L2,"")

I need to copy/paste these formulas down about 50 rows, Without the
formulas correcting their cell references. Is this possible?
 
G

Gazeta

Uzytkownik said:
Hi, I have a few hundred of formulas such as this throughout my excel
file: =IF("L2">1,L2,"")

I need to copy/paste these formulas down about 50 rows, Without the
formulas correcting their cell references. Is this possible?
Click to expand...
=IF("L2">1,L2,"")
use $l2 or l$2 or $l$2
try all of them and see behaviour of your formulas
u can use F4 to switch between those options ( when L2 in formula is
hihglighted )
mcg
 
G

Gord Dibben

Lock the cell references by adding $ signs.

IF("$L$2">1,$L$2,"")

To change many at a time use a macro. First macro is the one you need. The
others are just for you to keep in case.

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)
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)
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)
Next
End Sub

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

http://www.mvps.org/dmcritchie/excel/getstarted.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 the macros by going to Tool>Macro>Macros.

You can also assign these macros to a button or a shortcut key combo.



Gord Dibben MS Excel MVP
 
Top