Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Quick method to add absolute references in Excel using keyboard
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. |
#2
|
|||
|
|||
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. "photon63" wrote: 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. |
#3
|
|||
|
|||
Thanks - much appreciated :)
"Smuggy" wrote: 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. "photon63" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
quick books and excel 2002 | New Users to Excel | |||
Excel should have a quick and simple "change case" function like . | Excel Worksheet Functions | |||
Pivot tables Excel 2003 absolute references | Excel Discussion (Misc queries) | |||
Why is pointer and keyboard control is lost in certain Excel file. | Excel Discussion (Misc queries) |