Replace error with value

[MrThor]

Hi, I have a excel sheet with several columns with formulas in the cells. In some of the cells the message "#SAKNAS!" appears, I want to replace the value with "0". I tried the following code but it does not work.

    Cells.Replace What:="#SAKNAS!", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Fluff]

Why not just wrap the formula in Iferror
=IFERROR(your formula,0)

 

[MrThor]

yes, I know that one but if there is another way it would be great.

 

[Fluff]

This will replace all formula that result in an error with 0

Sub MrThor()
   Cells.SpecialCells(xlFormulas, xlErrors).Value = 0
End Sub