SOLVED

changing formulae with find replace

New Contributor

I would like to change

=GEOMEAN('Rates'!F4684:F4687)

to

=ISERROR(GEOMEAN('Rates'!F4684:F4687),"") 

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)
Solution

@kered1957 

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
            Do
                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.