Absolute Cell Reference Across A Range

  • Thread starter Thread starter SamuelT
  • Start date Start date
S

SamuelT

Hi all,

Can anyone tell if there is a means of setting a range of cells (fo
example a1:c20) as absolute cell references without going into eac
cell and F4-ing.

I'm copying and pasting a bunch of tables where the whole table need
to have absolute references, and it's a pain in the backside having t
go through each cell and press F4!

Any help would be greatly appreciated,

Samuel
 
from a posting by Gord

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

Thanks for that. Unfortunately, Excel doesn't like the line:

If c.HasFormula Then

Any ideas?

TIA,

Samuel
 
A small typo...

Sub SetAllToAbsolute()
Dim c As Range
For Each c In Selection '<--Cell became C
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next c '<-- I like it here, too <bg>
End Sub
 
Don & Dave,

Many thanks - you've saved me hours of tapping F4! That's working a
treat now!

SamuelT
 
Back
Top