Reference Types (2000)

Topic

New Reply

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?

Reply | Quote

Replies

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 it! –Sam

Reply | Quote

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!)

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

Reply | Quote

Good solution, Mike. Never knew that the ConvertFormula method existed. –Sam

Reply | Quote

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.

Reply | Quote

Mike and all, thanks for this. One of those things I periodically need but never got around to figuring out.

Reply | Quote

Hi Mike,
If you’re doing a lot of cells, you might find this quicker (sorry it’s a bit late!):

Sub ChangeRefType()
    With Sheets("OrderGuide").Range("b58:d410")
        .Formula = Application.ConvertFormula(Formula:=.Formula, _
        fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsolute)
    End With
End Sub

FWIW.

Reply | Quote

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.

Reply | Quote

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:

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

FWIW.

Reply | Quote

What, you’re not going to write a toggle based on the first cell?

Reply | Quote

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…

Reply | Quote

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!

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

Good idea Rory, thanks!

Reply | Quote

Mike,
If you’re anything like me, I also recommend grouping some of the items onto popup menus on the shortcut menu – I have about 50 controls on mine and it was getting a little crowded!!

Reply | Quote

That is exactly what I did. I am going to put all of my customizations in this addin, cause on occasion I have found it necessary to delete the .xlb toolbar file to solve a problem here and there.

Reply | Quote

I suck at coding, however, here’s a WAG on the Reference Toggle with some documentation. Just works from whatever is in the first selected cell. Incompletely tested.

Sub FormulaRefToggle()
Dim strFCFormula As String
Dim intRef As Integer
Dim rngSel As Range
On Error Resume Next
Set rngSel = Intersect(ActiveSheet.UsedRange, Selection).SpecialCells(xlCellTypeFormulas, 7)
If Not rngSel Is Nothing Then
strFCFormula = Cells(rngSel.Row, rngSel.Column).FormulaR1C1
‘ xlAbsolute 1
‘ xlAbsRowRelColumn 2
‘ xlRelRowAbsColumn 3
‘ xlRelative 4
intRef = 1 + (1 – CInt(InStr(strFCFormula, “R[“) > 0) * 2 – CInt(InStr(strFCFormula, “C[“) > 0)) Mod 4
‘MsgBox ActiveCell.FormulaR1C1 & vbLf & intRef
rngSel.Formula = Application.ConvertFormula(Formula:=rngSel.Formula, _
fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=intRef)
End If
End Sub

If there is a function in the first cell in the selection, this sets all formulas to use the same function, which is a major danger since there is no undo with VBA. I need to research Application.ConvertFormula. Please improve!

Reply | Quote

John,
The major problem (or at least the first one I ran into) is that references relative to the same row or column don’t have the [ in them – e.g. RC[2] or R[2]C. The latter seems to be trickier to allow for. For the time being, as I’m swamped at work too, I think I’ll stick with my 4 separate macros! Though you could combine them and pass a parameter to the sub from the Tag of the commandbarcontrol…..

Reply | Quote

John, How about this version.
First put this at the top of the module to contain the sub that follows:

 
Private Type KeyboardBytes
    kbByte(0 To 255) As Byte
End Type

Dim kbArray As KeyboardBytes

Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Long
Private Declare Function GetKeyboardState Lib "user32" (kbArray As KeyboardBytes) As Long
Private Declare Function SetKeyboardState Lib "user32" (kbArray As KeyboardBytes) As Long

Const VK_NUMLOCK As Integer = &H90
Const VK_SHIFT As Integer = &H10
Const VK_CONTROL As Integer = &H11
Const VK_MENU As Integer = &H12 'Alt key
Const VK_CAPSLOCK As Integer = &H14

Now here’s the sub. Attach it to a button and run it this way. Hold down control & shift and click the button to get $A$1, just the control key and click gives $A1, just the shift key and click gives A$1, and just a plain click gives A1.

Sub RefSwitcher()
Dim ShiftState As Long
Dim CntrlState As Long

ShiftState = GetKeyState(VK_SHIFT) And 128
CntrlState = GetKeyState(VK_CONTROL) And 128
If ShiftState = 128 And CntrlState = 128 Then
    With Selection
        .Formula = Application.ConvertFormula(Formula:=.Formula, _
        fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsolute)
    End With
ElseIf CntrlState = 128 Then
    With Selection
        .Formula = Application.ConvertFormula(Formula:=.Formula, _
        fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlRelRowAbsColumn)
    End With
ElseIf ShiftState = 128 Then
    With Selection
        .Formula = Application.ConvertFormula(Formula:=.Formula, _
        fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsRowRelColumn)
    End With
Else
    With Selection
        .Formula = Application.ConvertFormula(Formula:=.Formula, _
        fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlRelative)
    End With
End If
End Sub

Ken

Reply | Quote