Macro to add if statment to every formula in a range

[tonywatsonhelp]

Hi Everyone,

Ok I hope this is posible,
I inherated a doc from an idiot who doesn't care about looks and I have lots of ranges that have simple link formulas in.
e.g.s "='EitWT'!D11"

a lot of these have blanks and I have a sheet full of zeros, I know I can hide the zeros but for calculation purposes etc. i'd prefer they were "",
so i'm look for a macro that does this
for every formula in selected range
change formula from

"='EitWT'!D11" to "=If(

'EitWT'!D11"="","",

'EitWT'!D11)"​

​

​

<strike></strike><strike></strike>

any ideas please.
thanks

Tony

 

[Norie]

Tony

Try this, adjust the range as required.

Dim rng As Range
Dim cl As Range

    For Each cl In Range("A1:A100").SpecialCells(xlCellTypeFormulas)
        cl.Formula = "=IF(" & Mid(cl.Formula, 2) & "="""", """", " & Mid(cl.Formula, 2) & ")"
    Next cl

 

[tonywatsonhelp]

Brilliant Norie, thanks very much
Tony