Page 1 of 1
Changing formula using VBA
Posted: August 25th, 2006, 3:48 am
by 789456
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
Changing formula using VBA
Posted: August 25th, 2006, 5:07 am
by mrowell
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
Changing formula using VBA
Posted: August 25th, 2006, 6:04 am
by 789456
Thanx MarkBut the macros returns "application defined or object defined error"
Changing formula using VBA
Posted: August 25th, 2006, 7:00 am
by gjlipman
You need the ,0 in the round formula, ie ActiveCell.Formula = "=ROUND(" & RIGHT(ActiveCell.Formula, Len(ActiveCell.Formula)-1) & ",0)"
Changing formula using VBA
Posted: August 25th, 2006, 9:03 am
by mrowell
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