Quick method to add absolute references in Excel using keyboard

P

photon63

It would be useful for absolute references to be added in Excel when holding
a key down on the keyboard, instead of having to re-edit each cell every
time. Say, hold down the mouse button, press 'r' to toggle rows between
absolute and relative, and the same for 'c' and columns.
 
S

Smuggy

Here's an excellent routine for you, gives you two subroutines Reltoabs and
Abstorel which you could then assign to keyboard shortcut or to an icon on
toolbar...

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


Steve
PS. I wish I could claim the credit for this, but I can't - thought I would
post it for you anyway.
 
P

photon63

Thanks - much appreciated :)

Smuggy said:
Here's an excellent routine for you, gives you two subroutines Reltoabs and
Abstorel which you could then assign to keyboard shortcut or to an icon on
toolbar...

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


Steve
PS. I wish I could claim the credit for this, but I can't - thought I would
post it for you anyway.
Click to expand...
 
Top