relative to absolute

[dennisli]

Good morning,
My current formula is relative, such as: a4+b689+c9+d6+f55. I want to change the formula to absolute format: a$4+ b$689+c$9+d$6+f$55. Right now, I just add $ to each content. That means, I have to manually enter five $. It’s really time consuming.
Are there some short cuts to do this?
Thanks lot in advance.
Dennis

 

[macleanb]

Yes - select the items in the edit bar and then use the F4 key which will cycle through the different abs/rel combinations.

You can select the entire lot and do it in one go

 

[dennisli]

I highlight the cell containing the formula and then hit F4 but the formula is gone. If you have chance, could you tell me a little more about it?
Thanks.

 

[macleanb]

Hi

I have now idea why your formula has gone - as the F4 function key doesnt have that effect for me. But the way you should use it for this purpose is that you should have the text of the formula selected in the edit bar before hittiong F4.

 

[dennisli]

Hi, Ben,
What is edit bar?
I checked Excel help and noticed that F4 or Ctrl + Y is: repeat for last action.
Thanks lot.
Dennis

 

[Smitty]

Here's some code from Dave Hawley at www.ozgrid.com that will let you toggle absolutes in a selected range:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> MakeAbsoluteorRelative()
    <SPAN style="color:#007F00">'   Written by OzGrid Business Applications</SPAN>
    <SPAN style="color:#007F00">'   www.ozgrid.com</SPAN>
    
    <SPAN style="color:#00007F">Dim</SPAN> RdoRange <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Reply <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    <SPAN style="color:#007F00">'   Ask whether Relative or Absolute</SPAN>
    Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _
    & "Relative row/Absolute column = 1" & Chr(13) _
    & "Absolute row/Relative column = 2" & Chr(13) _
    & "Absolute all = 3" & Chr(13) _
    & "Relative all = 4", "OzGrid Business Applications")
    
    <SPAN style="color:#007F00">'   They cancelled</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Reply = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#007F00">'   Set Range variable to formula cells only</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> RdoRange = Selection.SpecialCells(Type:=xlFormulas)
    
    <SPAN style="color:#007F00">'   Determine the change type</SPAN>
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Reply
    <SPAN style="color:#00007F">Case</SPAN> 1  <SPAN style="color:#007F00">'    Relative Row/Absolute Column</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> RdoRange.Areas.Count
        RdoRange.Areas(i).Formula = _
        Application.ConvertFormula _
        (Formula:=RdoRange.Areas(i).<SPAN style="color:#00007F">For</SPAN>mula, _
        FromReferenceStyle:=xlA1, _
        ToReferenceStyle:=xlA1, <SPAN style="color:#00007F">To</SPAN>Absolute:=xlRelRowAbsColumn)
    <SPAN style="color:#00007F">Next</SPAN> i
    
    <SPAN style="color:#00007F">Case</SPAN> 2  <SPAN style="color:#007F00">'   Absolute Row/Relative Column</SPAN>
    
    For i = 1 To RdoRange.Areas.Count
        RdoRange.Areas(i).Formula = _
        Application.ConvertFormula _
        (<SPAN style="color:#00007F">For</SPAN>mula:=RdoRange.Areas(i).Formula, _
        FromReferenceStyle:=xlA1, _
        <SPAN style="color:#00007F">To</SPAN>ReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
    <SPAN style="color:#00007F">Next</SPAN> i
    
    <SPAN style="color:#00007F">Case</SPAN> 3  <SPAN style="color:#007F00">'   Absolute All</SPAN>
    
    For i = 1 <SPAN style="color:#00007F">To</SPAN> RdoRange.Areas.Count
        RdoRange.Areas(i).Formula = _
        Application.Convert<SPAN style="color:#00007F">For</SPAN>mula _
        (Formula:=RdoRange.Areas(i).Formula, _
        FromReferenceStyle:=xlA1, _
        ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
    <SPAN style="color:#00007F">Next</SPAN> i
    
    <SPAN style="color:#00007F">Case</SPAN> 4  <SPAN style="color:#007F00">'    Relative All</SPAN>
    
    For i = 1 To RdoRange.Areas.Count
        RdoRange.Areas(i).Formula = _
        Application.ConvertFormula _
        (Formula:=RdoRange.Areas(i).Formula, _
        FromReferenceStyle:=xlA1, _
        ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
    <SPAN style="color:#00007F">Next</SPAN> i
    
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">' Typo</SPAN>
        MsgBox "Change type not recognised!", vbCritical, _
        "OzGrid Business Applications"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    
    <SPAN style="color:#007F00">'   Clear memory</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> RdoRange = <SPAN style="color:#00007F">Nothing</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

As for Edit mode, in your formula cell, hit F2. Then you can F4 on any given range to toggle absolutes.

HTH,

Smitty

 

[dennisli]

wonderful. thanks lot.