Howdy,
I'm trying to convert multiple formulas in order to make them absolute, so that I can copy and paste them into other worksheets without the formulas changing e.g. from A1 to $A$1.
After searching this forum I found this bit of code:
Sub test()
Dim c As Range
For Each c In Selection
c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
Next
End Sub
It works great unless there is a table reference or alike in the formula in which case the cell is converted to "#value".
e.g.
=D164+SUMIFS(CR.Table[Expenses Cost (OPEX)],CR.Table[Status],"Forecast",CR.Table[Project Phase],"Phase 2/3/4")
Does anyone know if there is a way to ignore/only update what's required please?
Any help would be greatly appreciated. (My VBA skills suck)
Nick
I'm trying to convert multiple formulas in order to make them absolute, so that I can copy and paste them into other worksheets without the formulas changing e.g. from A1 to $A$1.
After searching this forum I found this bit of code:
Sub test()
Dim c As Range
For Each c In Selection
c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
Next
End Sub
It works great unless there is a table reference or alike in the formula in which case the cell is converted to "#value".
e.g.
=D164+SUMIFS(CR.Table[Expenses Cost (OPEX)],CR.Table[Status],"Forecast",CR.Table[Project Phase],"Phase 2/3/4")
Does anyone know if there is a way to ignore/only update what's required please?
Any help would be greatly appreciated. (My VBA skills suck)
Nick
