Absolute to Relative Conversion ends in #value!

Hi Excel Masters,

I have got a problem that has causes some significant headache for me:

I have the following formula in one of my cells:

='xxxxxxxxx\[sample file.xlsx]sample'!$O$7

and I have tried to convert it into a relative reference, so the end result will look like this:

='xxxxxxxxx\[sample file.xlsx]sample'!O7

I used the below redacted code but excel is returning " #value! " .... Please help!!

Sub Ab_to_Rel()
   
   Range("O7").Select
   
   Selection.Formula = Application.ConvertFormula(Selection.Formula, xlR1C1, xlA1, xlRelative)
   
End Sub

Please let me know if you need additional information.

Many thanks.

The Heggins.

Re: Absolute to Relative Conversion ends in #value!

Worked BEAUTIFULLY.

Thank you for your help on both posts Master Mike.

Quote from mikerickson;776063

The FromStyle argument has to match the string given. Try either

Code

Selection.Formula = Application.ConvertFormula(Selection.FormulaR1C1, xlR1C1, xlA1, xlRelative) 


' or
Selection.Formula = Application.ConvertFormula(Selection.Formula, xlA1, xlA1, xlRelative)