Sep 24 2021 08:00 AM - edited Sep 24 2021 08:09 AM
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
Sep 24 2021 08:22 AM - edited Sep 24 2021 08:22 AM
SolutionYou 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
Sep 24 2021 09:15 AM
Sep 24 2021 08:22 AM - edited Sep 24 2021 08:22 AM
SolutionYou 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