Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reference Types (2000)

    Is there a way to change a selection of cells which contain relative references to absolute ones without typing "$" in front of the row and column ranges in each cell?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reference Types (2000)

    Select the text of the cell(s) in the formula and press F4. Subsequent, F4's will cycle thru all the absolute/relative possibilities. Whatever text is selected, partially selected, or has the insertion point next to it will be changed. This means that if you type =a1 and press F4, the formula becomes =$A$1. Try it, you'll <img src=/S/love.gif border=0 alt=love width=15 height=15> it! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference Types (2000)

    Thanks Sammy, that is a pretty neat trick, but for the amount of cells I needed to convert, it was fairly lenthy. This is what I finally did using VBA (there may be a cleaner way to do it, but, hey, it worked!)

    <pre>Sub ChangeRefType()
    Dim cell As Range
    For Each cell In ThisWorkbook.Sheets("OrderGuide").Range("b58:d410" )
    cell.Formula = Application.ConvertFormula(Formula:=cell.Formula, _
    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsolute)
    Next
    End Sub

    </pre>


  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reference Types (2000)

    Good solution, Mike. Never knew that the ConvertFormula method existed. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference Types (2000)

    Neither did I, took me longer to figure out how to use it than it would have taken me with the F4 method, but at least now I know it exists.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Reference Types (2000)

    Hi Mike,
    If you're doing a lot of cells, you might find this quicker (sorry it's a bit late!):
    <pre>Sub ChangeRefType()
    With Sheets("OrderGuide").Range("b58:d410")
    .Formula = Application.ConvertFormula(Formula:=.Formula, _
    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsolute)
    End With
    End Sub
    </pre>

    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Reference Types (2000)

    Mike and all, thanks for this. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> One of those things I periodically need but never got around to figuring out.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference Types (2000)

    True indeed, works faster I believe.

    A fairly ingenious person could attach it to the right click cell menu via an add-in install...be fairly cool to use the RangeSelection to change all the selected cells to or from absolute or relative! ...although I think I mostly find the need to change them to absolute.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Reference Types (2000)

    Mike,
    Good idea - I shall add it to my (already overcrowded) right-click menu....
    [later edit]
    For the sake of completeness, I added a popup menu with all four options on it:
    <pre>Sub AbsoluteRef()
    With Selection
    .Formula = Application.ConvertFormula(Formula:=.Formula, _
    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsolute)
    End With
    End Sub
    Sub RelativeRef()
    With Selection
    .Formula = Application.ConvertFormula(Formula:=.Formula, _
    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlRelative)
    End With
    End Sub
    Sub AbsColumnRef()
    With Selection
    .Formula = Application.ConvertFormula(Formula:=.Formula, _
    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlRelRowAbsColumn)
    End With
    End Sub
    Sub AbsRowRef()
    With Selection
    .Formula = Application.ConvertFormula(Formula:=.Formula, _
    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsRowRelColumn)
    End With
    End Sub
    </pre>

    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Reference Types (2000)

    What, you're not going to write a toggle based on the first cell? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Reference Types (2000)

    I thought about that but couldn't decide whether I wanted a simple Relative/Absolute toggle or a cycling through all 4 combinations option. I suppose I could do both... <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Reference Types (2000)

    My preference would be to make the toggle cycle in the order of Absolute, then Row, then Column, then Relative, and back around, much the way the F4 key works with a formula which starts out in Relative reference mode. Oh, and while you're at it ... just kidding! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference Types (2000)

    OK, got my addin up and running to make changes to the cell commandbar. My question is this, is there a better way to undo the changes other than resetting the cell commandbar? In other words, if I gave this to my worst enemy who had made some serious and in-depth changes to his right click menu, when he uninstalled my add-in, all of his changes would be lost. Is there a better way not to make any enemies in this fashion?

  14. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference Types (2000)

    Nevermind, I think I will try to use the caption property to delete only the controls I added upon addin uninstall. Surely not more that one control would have the same caption!

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Reference Types (2000)

    Mike,
    If you assign each custom item that your add-in adds a particular Tag, your cleanup routine can then simply loop through each control, check the tag and remove it if appropriate. That should avoid the possibility of removing someone else's controls!
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •