Making relative references absolute

[Juan Sanchez]

Shari...

A reference in a formula i.e. A1 is relative
If you use $A$1 is absolute, If you use A$1 then Row is
absolute and column relative, all the way arround with $A1.

While in a formula if the cursos is on a reference then
pressing "F4" toggles through the varios absolute-relative
types...

Using names also is an absolute reference...

Take a look at XL Help for Names...

Hope this helps...

Cheers.
Juan

-----Original Message-----
Is there a way to 'wholesale' change a range of relative

references to absolute. Im trying to copy relative
references to a new location and it isnt working.

 

[Harlan Grove]

A reference in a formula i.e. A1 is relative
If you use $A$1 is absolute, If you use A$1 then Row is
absolute and column relative, all the way arround with $A1.

....

Reread the OP. It seems pretty clear the OP understands the difference but
was just looking for a way of converting relative references in *existing*
formulas to absolute.

 

[Dave R.]

Still working on this?

 

[Peo Sjoblom]

You would need a macro for that, I use 4 different macros,
press Alt + F11, click insert>module and paste in

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub

Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelative)
Next
End Sub

Sub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
Next
End Sub

Sub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelRowAbsColumn)
Next
End Sub


press Alt + Q to close the VBE, now select the formulas and run the first of
them by pressing Alt + F8 and select the macro. If you want to make it
available for future workbooks you can put the macro in your
Personal.xls

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Thanks Juan
I am familiar with the reference types, but I had never seen the F4 trick.

What I would like to do is select a range and change all from relative to
absolute

 

[Guest]

Tried your relto abs macro & got 'Compile error: Syntax Error. What am I doing wrong?

 

[Gord Dibben]

Montana

You are probably a victim of text-wrap.

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)

Should be all on one continuous line or have a continuation break(_) like

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, _
xlAbsolute)

Gord Dibben Excel MVP