Macro to edit formula

Posted by Sheena Rock on June 15, 2001 8:19 AM

I have a formula that I have already written in a worksheet. I want to create a macro that will do the following:

Edit the formula in the cell to add "Round((" at the beginning of the formula (no quotes) and skip to the end of the formula and put "),2)" (no quotes).

Each formula that needs editing is different, so the macro recorder did not work.

Is this possible?

Thanks in advance for any help!

Posted by Barrie Davidson on June 15, 2001 8:25 AM

The following will insert the rounding formula in the active cell.

Sub Insert_Rounding()
'Written by Barrie Davidson
Dim Orig_formula As String

Orig_formula = ActiveCell.Formula
Orig_formula = Mid(Orig_formula, 1, 1) & "round(" & Mid(Orig_formula, 2) & ",2)"
ActiveCell.Formula = Orig_formula

End Sub

Regards,
Barrie

Posted by Ivan F Moala on June 15, 2001 8:52 AM

If you want to do the change via selecting the
range of formula cells (Quicker) then this amend
to Barries should help out. Just select the formulas and run the macro.

Sub Insert_Rounding()
'Written by Barrie Davidson
Dim Orig_formula As String
Dim formulaRg As Range
Dim formcell As Range

For Each formcell In Selection
Orig_formula = formcell.Formula
Orig_formula = Mid(Orig_formula, 1, 1) & "round(" & Mid(Orig_formula, 2) & ",2)"
formcell.Formula = Orig_formula
Next
End Sub

HTH

Ivan

Posted by Barrie Davidson on June 15, 2001 8:58 AM

Nice improvement Ivan.....

Never even thought of it.

Thanks,
Barrie

Posted by Ivan F Moala on June 15, 2001 9:18 AM

Barry - you would have thought of that ;-)