'Convert_Reference Type Macro
'A Visual Basic module to convert absolute references to relative
'references or relative references to absolute references.
Sub Conv_RefType()
Dim Conv As String
'Prompt user to change to relative or absolute references
Conv = Application.InputBox _
("Type A to convert to Absolute, R to Relative Reference(s)", _
"Change Cell Reference Type")
'If changing relative to absolute references
If UCase(Conv) = "A" Then
'Loop through each cell selected
For Each Mycell In Selection
If Len(Mycell.Formula) > 0 Then
'Stores cell's formula as variable
MyFormula = Mycell.Formula
'Converts formula to absolute reference style
NewFormula = Application.ConvertFormula _
(Formula:=MyFormula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
toAbsolute:=xlAbsolute)
'Replaces old formula with new absolute formula
Mycell.Formula = NewFormula
End If
Next
'If changing absolute to relative references
ElseIf UCase(Conv) = "R" Then
'Loop through each cell selected
For Each Mycell In Selection
If Len(Mycell.Formula) > 0 Then
'Stores cell's formula as variable
MyFormula = Mycell.Formula
'Converts formula to relative reference style
NewFormula = Application.ConvertFormula _
(Formula:=MyFormula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
toAbsolute:=xlRelative)
'Replaces old formula with new relative formula
Mycell.Formula = NewFormula
End If
Next
'Display Error message if choice entered is invalid
ElseIf UCase(Conv) <> "FALSE" Then
MyMsg = "Enter A for Absolute, R for Relative Reference(s)"
MyTitle = "Option Not Valid"
MyBox = MsgBox(MyMsg, 0, MyTitle)
End If
End Sub
