Changing formula using VBA

789456 · August 25th, 2006, 3:48 am

Is there a way to edit formulas (different formulas) in a number of cells to include one common 'functional addition'. I want to change formulas in multiple cells to include a 'Round' function. For eg. Current formula in A1 is X2Current formula in A2 is Y30Current formula in A3 is Z40&Formula in A1 need to be =ROUND(X2,0)Formula in A2 need to be =ROUND(Y30,0)Formula in A3 need to be =ROUND(Z40,0)To give you a better understanding the formula I tried was: ActiveCell.Formula = "=ROUND(activecell.formula,0)"Cheers

mrowell · August 25th, 2006, 5:07 am

You probably need something likeActiveCell.Formula = "=ROUND(" & RIGHT(ActiveCell.Formula, Len(ActiveCell.Formula)-1) & ")"The RIGHT is needed to copy everything after the = sign in the existing formulaMark

789456 · August 25th, 2006, 6:04 am

Thanx MarkBut the macros returns "application defined or object defined error"

gjlipman · August 25th, 2006, 7:00 am

You need the ,0 in the round formula, ie ActiveCell.Formula = "=ROUND(" & RIGHT(ActiveCell.Formula, Len(ActiveCell.Formula)-1) & ",0)"

mrowell · August 25th, 2006, 9:03 am

QuoteOriginally posted by: gjlipmanYou need the ,0 in the round formula, ie ActiveCell.Formula = "=ROUND(" & RIGHT(ActiveCell.Formula, Len(ActiveCell.Formula)-1) & ",0)"Sorry...my mistake.. didn't test it first