Link to home
Avatar of AnalyticsTeam
AnalyticsTeamFlag for United States of America

asked on

Update a formula

I have a lot of formulas that I need to change. Example below:
The original formula says:
=SUM(B13:B16)

I need it to say:
=IF(SUM(B13:B16)=0,NA(),SUM(B13:B16))

How can I automate adding those sections to the formula? I can't do a drag or copy/paste because there are different sum ranges (the formulas sum 4 or 5 cells, it's not consistent).

I tried using a macro, but that doesn't seem to work because it bases the formula off the current cell reference, instead of what is currently in the cell formula.  

Can you help? Let me know if you need more info.
Thanks,
Elizabeth
Avatar of FernandoFernandes
FernandoFernandes
Flag of United States of America image

macro...
select a cell and run this code...
select the other and run this code... until you finish...

if there's too many cells, let me know, I can try to automate the process in a better way ...
Sub UpdateFormula
dim rng as range
dim strFormula as string
set rng = activecell
strFormula = rng.formula
strFormula = "=IF(" & strFormula & "=0,NA()," & strFormula & ")"
rngFormula = strFormula
set rng = nothing
End Sub

Open in new window

ops, my bad... use this code instead:

Sub UpdateFormula
dim rng as range
dim strFormula as string
set rng = activecell
strFormula = rng.formula
strFormula = "=IF(" & strFormula & "=0,NA()," & strFormula & ")"
rngFormula.Formula = strFormula
set rng = nothing
End Sub

Open in new window

SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of AnalyticsTeam

ASKER

Thank you guys!

I don't want it to run through the entire worksheet because I have other formulas that need to stay the same.

What do I need to change in line 7 for it to work?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
if you want one cell at a time, use the code on post 34908554, because it has the change on line 7 already :) and it works only on the active cell !
Ugh....I'm so sorry to keep bothering. I did just what you said and when I run the macro I get

"Run-time error '1004':

Application-defined or object-defined error


I see the range is set as the active cell, but it's not working? Any more advice? I've attached a file with an example.
Example-Formula-Change.xlsm
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
there were two problems:
1) rngFormula = strFormula was changed to rng.Formula = strFormula
2) when we take the original formula, we have to remove the "=" from its begining before applying to the IF() and put it inside the cell... none of us had thought of it, because we actually forgot :) It happens ...

please let us know if everything is ok now !
Ok that is cool. It works, thank you both so very much!
Great response times, thanks for helping it see it all the way through - I appreciate it guys!