MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Inserting formuals around linked cell by macro


Posted by Bryan on July 01, 2000 12:11 AM

Is there a way of inserting =Round() or other functions into a cell that has a formula, e.g. cell A4 has the following: +Sheet2!b4. The macro would leave the cell as: =Round(+Sheet2!b4,0). I need to round many cells like this.


Posted by Ivan Moala on July 01, 0100 1:14 AM


Try something along the lines of this;

ActiveF = Mid(ActiveCell.Formula, 2, Len(ActiveCell.Formula) - 1)
ActiveCell.Formula = "=ROUND(" & ActiveF & ",0)"


Ivan

Posted by Bryan on July 01, 0100 3:09 AM

Ivan, I've tried the macro as :
Sub InsertFormula()
ActiveF = Mid(ActiveCell.Formula, 2, Len(ActiveCell.Formula) - 1)
ActiveCell.Formula = "=ROUND(" & ActiveF & ",0)"
End Sub

but am geting ; Variable not defined.
Bryan

Posted by Bryan on July 01, 0100 3:17 AM


I've sussed it!! 'Dim ActiveF'

Thanks very much for your help,