Hi,
I'd like to convert all relative formulas to absolute in a range.
I found this working:
http://www.ozgrid.com/VBA/formula-ref-change.htm
However if some cell contains already all absolute references,
it turns to #VALUE error.
How could I find out if cell's formula is absolute or not?
Thank you
Re: Find out if formula is absolute
I was not able to duplicate the problem you mention when running the code. To double-check, this is the macro you're using?
Sub MakeAbsoluteorRelativeFast()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim RdoRange As Range
Dim i As Integer
Dim Reply As String
'Ask whether Relative or Absolute
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")
'They cancelled
If Reply = "" Then Exit Sub
On Error Resume Next
'Set Range variable to formula cells only
Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)
'determine the change type
Select Case Reply
Case 1 'Relative row/Absolute column
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
Next i
Case 2 'Absolute row/Relative column
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
Next i
Case 3 'Absolute all
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next i
Case 4 'Relative all
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
Next i
Case Else 'Typo
MsgBox "Change type not recognised!", vbCritical, _
"OzGrid Business Applications"
End Select
'Clear memory
Set RdoRange = Nothing
End SubRe: Find out if formula is absolute
Hi Luke,
yes, basically I used same method - Application.ConvertFormula
Private Sub Convert_Formulas_Absolute()
Dim r As Range
Dim ws As Worksheet
Dim i As Integer, j As Integer
For i = 3 To 11
Set ws = Worksheets(i)
Set r = ws.Range("F18", ws.Range("G82")).SpecialCells(xlCellTypeFormulas)
For j = 1 To r.Areas.Count
r.Areas(j).Formula = application.ConvertFormula( _
r.Areas(j).Formula, xlA1, xlA1, xlAbsolute)
Next j
Next i
End SubLet's say some cells of the range contain formula like this:
Then those cells after running the macro change result to #VALUE error.
Re: Find out if formula is absolute
Weird. I still can't duplicate. Can you test this file on your machine?
As you said before, it should be working.
Re: Find out if formula is absolute
Have you tried looping through cells rather than through Areas?
If you have formula cells with different formulas that are next to each other, the loop through Area would have problems.
Re: Find out if formula is absolute
Hi mikerickson,
same result whether I loop through areas or cells.
Cannot figure out why, I guess I need to find a workaround.
Don’t have an account yet? Register yourself now and be a part of our community!