VBA to change current formula

[rahmaddanis]

Dear all,
I have many cells with formula in it. I want to update the formula of the selected cells by adding if statement to the current formula to replace 0 result with "" (empty string).

Here what I want to achieve
=IF("current formula result"=0,"","current formula")

e.g
below formula return 0 because no data
=SUMIFS(TGR!$Z8:$Z62,TGR!$C8:$C62,$B$9,TGR!$X8:$X62,1)

I want to modify it so 0 value change to "", the formula should becomes
=IF(SUMIFS(TGR!$Z8:$Z62,TGR!$C8:$C62,$B$9,TGR!$X8:$X62,1)=0,"",=SUMIFS(TGR!$Z8:$Z62,TGR!$C8:$C62,$B$9,TGR!$X8:$X62,1))

How to do it easily in the shortest time?. appreciate your help.
thanks.

 

[Mike LH]

Hi,

Try this

Sub somesub()
Dim r, c As Range
For Each c In Selection
If c.HasFormula Then
r = Mid(c.Formula, 2)
c.Formula = "=IF(" & r & "=0,""""," & r & " )"
End If
Next
End Sub

 

[rahmaddanis]

Hi Mike,
Thank you, works like a charm.