Shortcut to change change cell reference to Absolute reference?
 

Once I create a formula and have tested it successfully is there a shortcut
to change some of the cell references to be absolute references or do I have
to arrow over and enter a $?

Shortcut to change change cell reference to Absolute reference?
 

You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or do I
have
to arrow over and enter a $?

Shortcut to change change cell reference to Absolute reference?
 

Hi Rich

In the formula bar press F4 with the curser in the cell address


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"richk" wrote in message ...
Once I create a formula and have tested it successfully is there a shortcut
to change some of the cell references to be absolute references or do I have
to arrow over and enter a $?

Shortcut to change change cell reference to Absolute reference?
 

You could do a find and replace.

"richk" wrote:

Once I create a formula and have tested it successfully is there a shortcut
to change some of the cell references to be absolute references or do I have
to arrow over and enter a $?

Shortcut to change change cell reference to Absolute reference
 

Thanks for the information. It was exactly what I was looking for.

"Peo Sjoblom" wrote:

You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or do I
have
to arrow over and enter a $?

Shortcut to change change cell reference to Absolute reference
 

This is a great trick. However, what do you do if you have 3,250 cells you
want to convert to absolute? Do I have to click on each cell, click in
formula bar and hit F4 in all 3,250 cells?

"Peo Sjoblom" wrote:

You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or do I
have
to arrow over and enter a $?

Shortcut to change change cell reference to Absolute reference
 

You could use this macro (but make sure to read the note at the end)...

Sub ConvertAllReferencesToAbsolute()
Dim S As Range, C As Range, A As Variant, Addresses() As String
For Each S In Selection
If S.HasFormula Then
S.Formula = Replace(S.Formula, "$", "")
Addresses = Split(S.Precedents.Address, ",")
For Each A In Addresses
For Each C In Range(A)
S.Formula = Replace(S.Formula, C.Address(False, _
False), C.Address(True, True))
Next
Next
End If
Next
End Sub

Note: If any of your formulas have text constants in them that contain $
signs, those $ signs will be removed; so, you can't use the above on cells
with text constants containing $ signs. There is a way around this problem,
but it makes the macro take longer to execute do to the overhead associated
with the needed additional code.

--
Rick (MVP - Excel)


"Terri" wrote in message
...
This is a great trick. However, what do you do if you have 3,250 cells you
want to convert to absolute? Do I have to click on each cell, click in
formula bar and hit F4 in all 3,250 cells?

"Peo Sjoblom" wrote:

You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or do I
have
to arrow over and enter a $?

Shortcut to change change cell reference to Absolute reference
 

Actually, maybe protecting text constants with $ signs in them is not all
that inefficient. Here is a macro to do that...

Sub ConvertAllReferencesToAbsolute()
Dim X As Long, S As Range, C As Range, A As Variant
Dim Parts() As String, Addresses() As String
For Each S In Selection
If S.HasFormula Then
Parts = Split(S.Formula, """")
For X = 1 To UBound(Parts) Step 2
Parts(X) = Replace(Parts(X), "$", Chr(1))
Next
S.Formula = Join(Parts, """")
S.Formula = Replace(S.Formula, "$", "")
Addresses = Split(S.Precedents.Address, ",")
For Each A In Addresses
For Each C In Range(A)
S.Formula = Replace(S.Formula, C.Address(False, _
False), C.Address(True, True))
Next
Next
S.Formula = Replace(S.Formula, Chr(1), "$")
End If
Next
End Sub

Oh, and I forgot to mention in my previously message... this macro works on
the Selected cells... if you have a defined range to work this code on, then
replace the Selection reference in this statement...

For Each S In Selection

with the specific range you want to use.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You could use this macro (but make sure to read the note at the end)...

Sub ConvertAllReferencesToAbsolute()
Dim S As Range, C As Range, A As Variant, Addresses() As String
For Each S In Selection
If S.HasFormula Then
S.Formula = Replace(S.Formula, "$", "")
Addresses = Split(S.Precedents.Address, ",")
For Each A In Addresses
For Each C In Range(A)
S.Formula = Replace(S.Formula, C.Address(False, _
False), C.Address(True, True))
Next
Next
End If
Next
End Sub

Note: If any of your formulas have text constants in them that contain $
signs, those $ signs will be removed; so, you can't use the above on cells
with text constants containing $ signs. There is a way around this
problem, but it makes the macro take longer to execute do to the overhead
associated with the needed additional code.

--
Rick (MVP - Excel)


"Terri" wrote in message
...
This is a great trick. However, what do you do if you have 3,250 cells
you
want to convert to absolute? Do I have to click on each cell, click in
formula bar and hit F4 in all 3,250 cells?

"Peo Sjoblom" wrote:

You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or do
I
have
to arrow over and enter a $?

Shortcut to change change cell reference to Absolute reference
 

Here are 4 different macros from Gord Dibben:

Option Explicit
Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next Cell
End Sub
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 Cell
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)
End If
Next Cell
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)
End If
Next Cell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
Then you'll have to select the range of cells to fix and then hit alt-F8 (to see
the macro dialogs), pick the one you want and run it.

Terri wrote:

This is a great trick. However, what do you do if you have 3,250 cells you
want to convert to absolute? Do I have to click on each cell, click in
formula bar and hit F4 in all 3,250 cells?

"Peo Sjoblom" wrote:

You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or do I
have
to arrow over and enter a $?




--

Dave Peterson

Shortcut to change change cell reference to Absolute reference
 

ConvertFormula... I just **knew** there had to be a method for this... I
simply missed it when I went looking for it.

I would consider collapsing the four macros into a single subroutine that
would be called from one's own macro. Here is the subroutine...

Sub SetReferenceStyle(RefStyle As XlReferenceType)
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then Cell.Formula = _
Application.ConvertFormula(Cell.Formula, xlA1, xlA1, RefStyle)
Next Cell
End Sub

and you would call it like this...

SetReferenceStyle xlRelRowAbsColumn

where the argument choices are (use either the number or the predefined
constant, not both)...

1 -- xlAbsolute
2 -- xlAbsRowRelColumn
3 -- xlRelRowAbsColumn
4 -- xlRelative

Because I declared RefStyle as XlReferenceType, VB's Intellisense will
present these to you in a selectable drop down box when calling the
subroutine.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
Here are 4 different macros from Gord Dibben:

Option Explicit
Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next Cell
End Sub
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 Cell
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)
End If
Next Cell
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)
End If
Next Cell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
Then you'll have to select the range of cells to fix and then hit alt-F8
(to see
the macro dialogs), pick the one you want and run it.

Terri wrote:

This is a great trick. However, what do you do if you have 3,250 cells
you
want to convert to absolute? Do I have to click on each cell, click in
formula bar and hit F4 in all 3,250 cells?

"Peo Sjoblom" wrote:

You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or do
I
have
to arrow over and enter a $?




--

Dave Peterson

Shortcut to change change cell reference to Absolute reference
 

Run this macro after selecting all cells with the relative references.

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


Gord Dibben MS Excel MVP

On Fri, 4 Dec 2009 11:12:01 -0800, Terri
wrote:

This is a great trick. However, what do you do if you have 3,250 cells you
want to convert to absolute? Do I have to click on each cell, click in
formula bar and hit F4 in all 3,250 cells?

"Peo Sjoblom" wrote:

You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or do I
have
to arrow over and enter a $?

Shortcut to change change cell reference to Absolute reference
 

And another change would be to only look at formulas in the selection:

dim myRng as range

set myrng = nothing
on error resume next
set myrng = intersect(selection, _
selection.cells.specialcells(xlcelltypeformulas))
on error goto 0

if myrng is nothing then
msgbox "Nothing to fix"
exit sub
end if

for each cell in myrng.cells
....

It could make a difference if there weren't many formulas in a large area--or if
the user selected whole columns...

Rick Rothstein wrote:

ConvertFormula... I just **knew** there had to be a method for this... I
simply missed it when I went looking for it.

I would consider collapsing the four macros into a single subroutine that
would be called from one's own macro. Here is the subroutine...

Sub SetReferenceStyle(RefStyle As XlReferenceType)
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then Cell.Formula = _
Application.ConvertFormula(Cell.Formula, xlA1, xlA1, RefStyle)
Next Cell
End Sub

and you would call it like this...

SetReferenceStyle xlRelRowAbsColumn

where the argument choices are (use either the number or the predefined
constant, not both)...

1 -- xlAbsolute
2 -- xlAbsRowRelColumn
3 -- xlRelRowAbsColumn
4 -- xlRelative

Because I declared RefStyle as XlReferenceType, VB's Intellisense will
present these to you in a selectable drop down box when calling the
subroutine.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
Here are 4 different macros from Gord Dibben:

Option Explicit
Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next Cell
End Sub
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 Cell
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)
End If
Next Cell
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)
End If
Next Cell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
Then you'll have to select the range of cells to fix and then hit alt-F8
(to see
the macro dialogs), pick the one you want and run it.

Terri wrote:

This is a great trick. However, what do you do if you have 3,250 cells
you
want to convert to absolute? Do I have to click on each cell, click in
formula bar and hit F4 in all 3,250 cells?

"Peo Sjoblom" wrote:

You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or do
I
have
to arrow over and enter a $?




--

Dave Peterson

--

Dave Peterson

Shortcut to change change cell reference to Absolute reference
 

Good idea! Simplifying the code a little bit...

Sub SetReferenceStyle(RefStyle As XlReferenceType)
Dim Cell As Range
On Error Resume Next
For Each Cell In Intersect(Selection, Selection.Cells. _
SpecialCells(xlCellTypeFormulas))
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, RefStyle)
Next Cell
End Sub

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
And another change would be to only look at formulas in the selection:

dim myRng as range

set myrng = nothing
on error resume next
set myrng = intersect(selection, _
selection.cells.specialcells(xlcelltypeformulas))
on error goto 0

if myrng is nothing then
msgbox "Nothing to fix"
exit sub
end if

for each cell in myrng.cells
....

It could make a difference if there weren't many formulas in a large
area--or if
the user selected whole columns...

Rick Rothstein wrote:

ConvertFormula... I just **knew** there had to be a method for this... I
simply missed it when I went looking for it.

I would consider collapsing the four macros into a single subroutine that
would be called from one's own macro. Here is the subroutine...

Sub SetReferenceStyle(RefStyle As XlReferenceType)
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then Cell.Formula = _
Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
RefStyle)
Next Cell
End Sub

and you would call it like this...

SetReferenceStyle xlRelRowAbsColumn

where the argument choices are (use either the number or the predefined
constant, not both)...

1 -- xlAbsolute
2 -- xlAbsRowRelColumn
3 -- xlRelRowAbsColumn
4 -- xlRelative

Because I declared RefStyle as XlReferenceType, VB's Intellisense will
present these to you in a selectable drop down box when calling the
subroutine.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
Here are 4 different macros from Gord Dibben:

Option Explicit
Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next Cell
End Sub
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 Cell
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)
End If
Next Cell
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)
End If
Next Cell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
Then you'll have to select the range of cells to fix and then hit
alt-F8
(to see
the macro dialogs), pick the one you want and run it.

Terri wrote:

This is a great trick. However, what do you do if you have 3,250 cells
you
want to convert to absolute? Do I have to click on each cell, click in
formula bar and hit F4 in all 3,250 cells?

"Peo Sjoblom" wrote:

You can highlight the formula in the formula bar and press F4, and
each
press will toggle from absolute, to absolute rows and relative
columns,
absolute column and relative rows and finally back to relative


--
Regards,

Peo Sjoblom


"richk" wrote in message
...
Once I create a formula and have tested it successfully is there a
shortcut
to change some of the cell references to be absolute references or
do
I
have
to arrow over and enter a $?




--

Dave Peterson

--

Dave Peterson