PDA

View Full Version : Solved: VB module to convert absolute references to relative



hendra
01-23-2008, 05:55 AM
Dear Gent's.

Could you please help me to modify the VB code of Excel macro to convert absolute references to relative references or relative references to absolute references. I want the module also can convert from relative references to $A4 or A$4, not only $A$4. And from $A4 or A$4 to A4.

Thanks so much.

Here's the code:

'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

Bob Phillips
01-23-2008, 06:31 AM
Sub Conv_RefType()
Dim Conv As String
Dim ConvertType As Long
Dim MyCell As Range
Dim NwFormula As String
Dim Msg As String

'Prompt user to change to relative or absolute references
Msg = "Type" & vbNewLine & _
" AA to convert to Absolute row/Absolute column, " & vbNewLine & _
" AR to Absolute row/Relative column, " & vbNewLine & _
" RA to Relative row/Absolute column, or , " & vbNewLine & _
" RR to Relative row/Relative column Reference(s)"
Conv = Application.InputBox(Msg, "Change Cell Reference Type")
'Loop through each cell selected
For Each MyCell In Selection
If MyCell.HasFormula Then

'If changing relative to absolute references
Select Case UCase(Conv)

Case "AA": ConvertType = xlAbsolute

Case "AR": ConvertType = xlAbsRowRelColumn

Case "RA": ConvertType = xlRelRowAbsColumn

Case "RR": ConvertType = xlRelative

'Display Error message if choice entered is invalid
Case Else
MsgBox Msg, 0, "Option Not Valid"
Exit For
End Select

MyCell.Formula = Application.ConvertFormula( _
Formula:=MyCell.Formula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
toAbsolute:=ConvertType)
End If
Next MyCell
End Sub