changing formulae with find replace

New Contributor

I would like to change




to avoid getting #NUM! errors. If I use Find and Replace I get formula error warning as each cell (there are thousands) contains different cell references.

Can anyone suggest a solution?
Many thanks

2 Replies
best response confirmed by kered1957 (New Contributor)


You could run this macro:


Sub FixGeomean()
    Dim rng As Range
    Dim adr As String
    Application.ScreenUpdating = False
    With Cells
        Set rng = .Find(What:="=GEOMEAN(", LookIn:=xlFormulas, LookAt:=xlPart)
        If Not rng Is Nothing Then
            adr = rng.Address
                rng.Formula = "=IFERROR(" & Mid(rng.Formula, 2) & ","""")"
                Set rng = .FindNext(After:=rng)
                If rng Is Nothing Then Exit Do
            Loop Until rng.Address = adr
        End If
    End With
    Application.ScreenUpdating = True
End Sub


You are a superstar, thanks very much.