Vba to set cell reference to absolute and back to original setting.

[glynn1969]

Hello trying to keep example simple here.

So in cells A1:A5 I have reference links to cells D1:D5.

I then have a macro to convert links to absolute values.

Sub test()
Dim c As Range
For Each c In Selection
c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
Next


End Sub

Then a second macro to convert back,

Sub test()
Dim c As Range
For Each c In Selection
c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlrelative)
Next

End Sub

however when I convert back the cell references change eg the Reference in A2 becomes "=D3" instead of back to "=D2", the reference in A3 becomes "=D5" instead of back to "=D3" etc

Any advice on what I'm doing wrong

 

[LazyBug]

Sub toAbs()
Dim c As Range
    For Each c In Selection
        c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlAbsolute)
    Next
End Sub

Sub toRel()
Dim c As Range
    For Each c In Selection
        c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlRelative)
    Next
End Sub

 

[glynn1969]

Sub toAbs()
Dim c As Range
    For Each c In Selection
        c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlAbsolute)
    Next
End Sub

Sub toRel()
Dim c As Range
    For Each c In Selection
        c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlRelative)
    Next
End Sub

Excellent thanks LazyBug... easy when you know how

 

[bebo021999]

Or swith between relative/absolute. single sub

Sub switch()
Dim c As Range
For Each c In Selection
    c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, IIf(InStr(c.Formula, "$"), xlRelative, xlAbsolute))
Next
End Sub