• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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
0
AnalyticsTeam
Asked:
AnalyticsTeam
  • 6
  • 4
  • 2
4 Solutions
 
FernandoFernandesCommented:
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

0
 
FernandoFernandesCommented:
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

0
 
StephenJRCommented:
Just to note that Fernando's code could be adapted to loop through all cells with a formula if that were needed. Btw missing a dot in line 7 above.
Sub UpdateFormula()

Dim rng As Range
Dim strFormula As String

For Each rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    strFormula = rng.Formula
    strFormula = "=IF(" & strFormula & "=0,NA()," & strFormula & ")"
    rng.Formula = strFormula
Next rng
Set rng = Nothing

End Sub

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
FernandoFernandesCommented:
Stephen,
Thanks for writing with the loop...

Indeed i had forgot the ".Formula", it happens !  :)
AnalyticsTeam, enjoy ! :-)

p.s: do you know how to set up a macro to run ?
Alt+F11
Ctrl+R
Right-Click / Insert / Module
Paste the code
With the cursos anywhere within the new code, hit F5
0
 
AnalyticsTeamAuthor Commented:
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?
0
 
StephenJRCommented:
If you just want one cell at a time, you need this:

rng.Formula = strFormula

0
 
FernandoFernandesCommented:
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 !
0
 
AnalyticsTeamAuthor Commented:
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
0
 
FernandoFernandesCommented:
sorry, it was my bad.... Stephen tried to warn me and I didnt give proper attention.

this is the right code:
Sub UpdateFormula()
Dim rng As Range
Dim strFormula As String
Set rng = ActiveCell
strFormula = VBA.Right(rng.Formula, VBA.Len(rng.Formula) - 1)
strFormula = "=IF(" & strFormula & "=0,NA()," & strFormula & ")"
rng.Formula = strFormula
Set rng = Nothing
End Sub

Open in new window

0
 
FernandoFernandesCommented:
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 !
0
 
AnalyticsTeamAuthor Commented:
Ok that is cool. It works, thank you both so very much!
0
 
AnalyticsTeamAuthor Commented:
Great response times, thanks for helping it see it all the way through - I appreciate it guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now