Help with converting a block of cells with Absolute and mixed references to relative references
Don't have 2007 but these should work.
Sounds like you need the last one for all rleative.
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
Gord Dibben MS Excel MVP
On Thu, 13 Dec 2007 12:43:40 -0800, "Vulcan" wrote:
Sorry, forgot to mention:
Office 2007 on Vista Business
"Vulcan" wrote in message
...
Hoping that someone can offer some advice on this....
I'm creating a complex excel sheets with lots of relative, mixed, and
absolute references. Once I'm done, I'd like to copy a block within the
sheet to create a new block. The trouble is that all the absolute
references within the block are pointing to reference within the old
block. I can fix them manually, but I want to copy the block several
times, and it's a lot of work to change each one
What I'd like to to do is, once things are as I want them, convert all of
the Absolute references in the completed block to relative so that when I
past a new block, the addressing will be correct. One trick I've found is
that I can convert cells formulas from Absolute to relative one by one
using the F4 key, but that's not ideal either (although, it's easier than
editing each cell). Anyone know if there is a way to convert a block of
cells to relative mode in one shot? Thanks for any advice.
|