Prev: concatenate will not paste special
Next: Simple hack to get $500 to your home.
From: jchick0909 on 2 Jun 2010 13:03
i have 235 lines each linking to a cell in another tab. I need to make each
cell an absolute reference so i can copy them down again below and the same
cell is referenced.

How to I insert Absolute Reference in multiple cells. (i've tried macro's
but i failed).

Thanks in Advance
From: Gord Dibben on 2 Jun 2010 13:14
What failed when you tried macros?

Try one of these macros.

Select a range of cells then run the macro.

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 Wed, 2 Jun 2010 10:03:05 -0700, jchick0909
<jchick0909(a)discussions.microsoft.com> wrote:

>i have 235 lines each linking to a cell in another tab. I need to make each
>cell an absolute reference so i can copy them down again below and the same
>cell is referenced.
>
>How to I insert Absolute Reference in multiple cells. (i've tried macro's
>but i failed).
>
>Thanks in Advance

 | 
Pages: 1
Prev: concatenate will not paste special
Next: Simple hack to get $500 to your home.