Adding to an existing formula

[JPS]

I inherited a workbook that has about 20 spreadsheets and I need to add a
round function to 250+ formulas in one of the spreadsheets. The formulas are
in a set range but their references are not relative to allow me to update
one formula and then copy to other cells. A coworker and I have concluded
that each formula will need to be updated individually. Does anyone know of a
shortcut to update all of these formulas?

Thanks,

 

[JE McGimpsey]

One way:

http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/
thread/a1c45338385e0fff/0afe4bc0c46218c8?lnk=st&q=#0afe4bc0c46218c8

or, equivalently,

http://tinyurl.com/4mdwn2

 

[Gary''s Student]

Select the cells you want to modify and run this macro:

Sub rounder()
s1 = "=ROUND(("
s2 = "),0)"
For Each r In Selection
v = r.Formula
l = Len(v) - 1
r.Formula = s1 & Right(v, l) & s2
Next
End Sub


A cell containing:
=A1+A2
will become:
=ROUND((A1+A2),0)